Forums | Admin Discussion Forums: open-discussion Start New Thread Nested Flat Threaded Ultimate Show 25 Show 50 Show 75 Show 100   By: Alex Pitigoi RE: Inspecting a Database [ reply ]   2007-11-30 17:22 Glad to hear it all worked out in the end. You may also want to know, that the long waited fix to allow proper flow of the -805 code error (and others) during metadata retrieval is undergoing review and tests and will be available shortly as IBM_DB-0.9.2 along with another 2 fixes. By: Sean Wilbur RE: Inspecting a Database [ reply ]   2007-11-30 16:55 Dave, Yes, the file is not in my local Admin Client install, but since that seemed strange I went and looked at the server where I should have looked in the first place it is there, sorry for the confusion. After running all the command originally suggested by Alex yesterday, I can use the tables and columns commands. Thank you very much for your help Alex. -Sean By: David Adler RE: Inspecting a Database [ reply ]   2007-11-30 16:28 When I've had similar problems, I've found that binding db2schema.bnd fixed the problem. You don't have this file in sqllib/bnd? By: Alex Pitigoi RE: Inspecting a Database [ reply ]   2007-11-30 16:18 Sean, until I recreate your problem, I was wondering if you have tried all suggestions described in the Info Center: http://publib.boulder.ibm.com/infocenter/db2luw/v8/topic/com.ibm.db2.udb.doc/ad/r0007866.htm By: Sean Wilbur RE: Inspecting a Database [ reply ]   2007-11-30 16:09 Alex, Thanks for the information looks like you found my problem, I just installed this version of the admin development client and it does not appear to have a <...>\SQLLIB\bnd\db2schema.bnd file there are numerous other *.bnd and *.lst files but not that one? I ran: db2 bind @db2cli.lst blocking all grant public http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0004147.htm Seems like this is for the same purpose? But still no luck... looking at the trace I see the same error that you noted. [11/30/2007 11:05:13.689595] SQLGetDiagRec( pszSqlState="38553", pfNativeError=-443, pszErrorMsg="[IBM][CLI Driver][DB2/NT] SQL0443N Routine "SYSIBM.SQLCOLUMNS" (specific name "COLUMNS") has returned an error SQLSTATE with diagnostic text "SYSIBM:CLI:-805". SQLSTATE=38553 ", pcbErrorMsg=178 ) [11/30/2007 11:05:13.698185] <--- SQL_SUCCESS Time elapsed - +7.255800E-002 seconds -Sean By: Alex Pitigoi RE: Inspecting a Database [ reply ]   2007-11-29 17:59 Sean, the trace shows something here: SQLGetDiagRec( pszSqlState="38553", pfNativeError=-443, pszErrorMsg="[IBM][CLI Driver][DB2/NT] SQL0443N Routine "SYSIBM.SQLTABLES" (specific name "TABLES") has returned an error SQLSTATE with diagnostic text "SYSIBM:CLI:-805". SQLSTATE=38553 And that is related to the need to rebind packages after a FixPak refresh: "Possible causes for this message (-805) are: * The package was not bound or it was dropped. * If attempting to run a DB2 utility or CLI application, the DB2 utilities may need to be rebound to the database. * '%.pkgname' if the CURRENT PACKAGE PATH is set but no package with the name 'pkgname' could be found in any of the schemas in the CURRENT PACKAGE PATH" A solution was also described on a previous thread (RE: Any testing on DB2EC 912?) and is documented also here: http://www-1.ibm.com/support/docview.wss?uid=swg21215626 $ cd <DB2-install-path>/sqllib/bnd $ db2 terminate $ db2 connect to <database-name> $ db2 bind @db2cli.lst blocking all grant public $ db2 bind <db2-path>/sqllib/bnd/db2schema.bnd blocking all grant public sqlerror continue $ db2 terminate I hope this helps, but please let us know if otherwise. Thanks. By: Sean Wilbur RE: Inspecting a Database [ reply ]   2007-11-29 15:55 Alex, I ran the tracing you suggested and sent you the output in email. C:\Program Files\IBM\SQLLIB\BIN>db2 GET CLI CFG FOR SECTION COMMON Section: COMMON ------------------------------------------------- TraceTimeStamp=3 TraceFlush=1 TraceComm=1 TracePathName=c:\db2cl1.trace Trace=1 C:\working\devel\ruby>irb irb(main):001:0> require 'mswin32/ibm_db' => true irb(main):002:0> conn = IBM_DB::connect 'DRIVER={IBM DB2 ODBC DRIVER};DATABASE=cq001;HOSTNAME=<server>;PORT=50000;PROTOCOL=TCPIP;UID=cqtest;PWD=<***>;', '', '' => #<IBM_DB::Connection:0x2d7f1c0> irb(main):003:0> stmt = IBM_DB::tables conn, '', '%' => false irb(main):004:0> -Sean By: Alex Pitigoi RE: Inspecting a Database [ reply ]   2007-11-29 15:01 Sean , can you please take a CLI trace for the following test: irb(main):001:0> require 'mswin32/ibm_db' => true irb(main):002:0> conn = IBM_DB::connect 'DRIVER={IBM DB2 ODBC DRIVER};DATABASE=dbname;HOSTNAME=name.or.ipaddr;PORT=50000;PROTOCOL=TCPIP;UID=usrid;PWD=secret;', '', '' => #<IBM_DB::Connection:0x2f38f48> irb(main):003:0> stmt = IBM_DB::tables conn, '', '%' => #<IBM_DB::Statement:0x2f35e4c> irb(main):004:0> while s = IBM_DB::fetch_assoc(stmt) irb(main):005:1> p s irb(main):006:1> end To enable CLI trace: $ db2 UPDATE CLI CFG FOR SECTION COMMON USING Trace 1 $ db2 UPDATE CLI CFG FOR SECTION COMMON USING TracePathName <path> $ db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceComm 1 $ db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceFlush 1 $ db2 UPDATE CLI CFG FOR SECTION COMMON USING TraceTimeStamp 3 $ db2 GET CLI CFG FOR SECTION COMMON By: Alex Pitigoi RE: Inspecting a Database [ reply ]   2007-11-29 14:50 Sean, the test below was run against DB2 9.5.0 but it should certainly work against all DB2 8.1.15 or 9.1.2 and above. I shall verify and confirm this. I wonder, is there any other ibm_db driver call that fails besides IBM_DB::tables and IBM_DB::columns? By: Sean Wilbur RE: Inspecting a Database [ reply ]   2007-11-29 05:13 Alex, Thanks for the reply, are you doing this on v9 or v8? I tried your suggestions with all caps as I see in the when I use the db2 cmd prompt, but no luck? Also not sure why that wouldn't work for the tables command? -Sean By: Alex Pitigoi RE: Inspecting a Database [ reply ]   2007-11-28 22:51 Hi Sean, There are a few things you should be aware: 1. Passing '%' would certainly return all column metadata for a specified table name ('users'), but that may not be very efficient if you happen to have such table name in many schemas 2. Catalog information in DB2 is case sensitive, therefore you might need to lookup 'USERS' Here's the kind of results you might expect to see: irb(main):019:0> result = IBM_DB::columns(conn, '', '%', 'XML_TEST') => #<IBM_DB::Statement:0xb7a56f74> irb(main):020:0> while r = IBM_DB::fetch_assoc(result) irb(main):021:1> p r irb(main):022:1> end {"ORDINAL_POSITION"=>1, "SQL_DATA_TYPE"=>4, "REMARKS"=>nil, "NULLABLE"=>1, "NUM_PREC_RADIX"=>10, "CHAR_OCTET_LENGTH"=>nil, "COLUMN_SIZE"=>10, "IS_NULLABLE"=>"YES", "DATA_TYPE"=>4, "BUFFER_LENGTH"=>4, "TYPE_NAME"=>"INTEGER", "TABLE_CAT"=>nil, "SQL_DATETIME_SUB"=>nil, "TABLE_SCHEM"=>"DB2INST1", "DECIMAL_DIGITS"=>0, "COLUMN_NAME"=>"ID", "COLUMN_DEF"=>nil, "TABLE_NAME"=>"XML_TEST"} {"ORDINAL_POSITION"=>2, "SQL_DATA_TYPE"=>12, "REMARKS"=>nil, "NULLABLE"=>1, "NUM_PREC_RADIX"=>nil, "CHAR_OCTET_LENGTH"=>50, "COLUMN_SIZE"=>50, "IS_NULLABLE"=>"YES", "DATA_TYPE"=>12, "BUFFER_LENGTH"=>50, "TYPE_NAME"=>"VARCHAR", "TABLE_CAT"=>nil, "SQL_DATETIME_SUB"=>nil, "TABLE_SCHEM"=>"DB2INST1", "DECIMAL_DIGITS"=>nil, "COLUMN_NAME"=>"DATA", "COLUMN_DEF"=>nil, "TABLE_NAME"=>"XML_TEST"} {"ORDINAL_POSITION"=>3, "SQL_DATA_TYPE"=>-370, "REMARKS"=>nil, "NULLABLE"=>1, "NUM_PREC_RADIX"=>nil, "CHAR_OCTET_LENGTH"=>0, "COLUMN_SIZE"=>0, "IS_NULLABLE"=>"YES", "DATA_TYPE"=>-370, "BUFFER_LENGTH"=>0, "TYPE_NAME"=>"XML", "TABLE_CAT"=>nil, "SQL_DATETIME_SUB"=>nil, "TABLE_SCHEM"=>"DB2INST1", "DECIMAL_DIGITS"=>nil, "COLUMN_NAME"=>"XMLCOL", "COLUMN_DEF"=>nil, "TABLE_NAME"=>"XML_TEST"} => nil irb(main):027:0> result = IBM_DB::tables(conn, '', '%') => #<IBM_DB::Statement:0xb7a46110> irb(main):028:0> while r = IBM_DB::fetch_assoc(result) irb(main):029:1> p r irb(main):030:1> end {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"DB2INST1", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"NEWAPP", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"NULLID", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"PYTHONIC", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"RAILS123", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"RAILS125", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"SQLJ", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"SYSCAT", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"SYSFUN", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"SYSIBM", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"SYSIBMADM", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"SYSIBMINTERNAL", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"SYSIBMTS", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"SYSPROC", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"SYSSTAT", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"SYSTOOLS", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"T", "TABLE_NAME"=>nil} {"TABLE_TYPE"=>nil, "REMARKS"=>nil, "TABLE_CAT"=>nil, "TABLE_SCHEM"=>"TEAMROOM", "TABLE_NAME"=>nil} => nil By: Sean Wilbur Inspecting a Database [ reply ]   2007-11-28 22:27 I am using 0.9.1 with DB2 v8.1.15.254 and trying to do some inspection prior to a formatted data dump. The returned statement for tables(...) and columns(...) is only returning null, any ideas? #### require 'mswin32/ibm_db' conn = IBM_DB::connect( "db001", "test", "***" ) unless conn puts "Connection failed." puts IBM_DB::conn_errormsg exit end puts "Connection succeded." # result = IBM_DB::columns(conn,'','%','users' ) result = IBM_DB::tables(conn,'','%') unless result puts " result is null " exit end while( record = IBM_DB::fetch_both( result ) ) p record print " \n" end IBM_DB::close(conn) ####