DB2 7.2
I am trying to connect to DB2 UDB 7.2 running on AIX. I installed the DB2 Run-Time Client Lite Version 8 fixpack 14 and was finally able to connect. Then, I was getting an error as soon as I clicked create... "Error: [IBM][CLI Driver][DB2 Platform] SQL0805N Package "NULLID.SYSSH200" was not found. SQLSTATE=51002, Connector 'db2', Method -Catalog[Metadata]- (-805 )" I found help article at http://www-1.ibm.com/support/docview.wss?uid=swg21163994 and doing that fixed the issue.
Now, I get this error...
Message
Index #0 Message: [IBM][DB2/6000] SQL0351N An unsupported SQLTYPE was encountered in position "16" of the output SQLDA (select list). SQLSTATE=56084
NativeError: -351 Source: IBM.Data.DB2
ERROR [56084] [IBM][DB2/6000] SQL0351N An unsupported SQLTYPE was encountered in position "16" of the output SQLDA (select list). SQLSTATE=56084
Stack Trace
-----[Core exception]-------------------- at SD.LLBLGen.Pro.DBDrivers.DB2.DB2SchemaRetriever.WrapAndThrowDB2Exception(DB2Exception ex) at SD.LLBLGen.Pro.DBDrivers.DB2.DB2SchemaRetriever.RetrieveAllSequences(DB2Connection openDB2Connection) at SD.LLBLGen.Pro.DBDrivers.DB2.DB2SchemaRetriever.RetrieveSchema(String connectionString, IDBSchema schemaToRetrieve, SchemaFetchFlags fetchFlags) at SD.LLBLGen.Pro.DBDrivers.DB2.DB2CatalogRetriever.RetrieveSchemas(String connectionString, IDBCatalog catalogToRetrieve, SchemaFetchFlags fetchFlags) at SD.LLBLGen.Pro.DBDrivers.DB2.DB2DBDriver.PopulateSelectedCatalogs(Hashtable callBacks, Hashtable connectionElements, SchemaFetchFlags fetchFlags, Hashtable properties) at SD.LLBLGen.Pro.Gui.Forms.NewProjectWizard.CreateNewProject() -----[InnerException]-------------------- at IBM.Data.DB2.DB2Connection.b(IntPtr A_0, m A_1, x A_2) at IBM.Data.DB2.DB2Command.a(CommandBehavior A_0, String A_1) at IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior) at IBM.Data.DB2.DB2Command.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
Source
SD.LLBLGen.Pro.DBDrivers.DB2DBDriver IBM.Data.DB2
Am I spinning my wheels trying to connect to 7.2? Should I just give up? 
Well, I may be SOL. I upgraded (uninstalled old, installed new) from the DB2 7.2 runtime client to the 8.x client lite that is linked to from this site before I started on this post.
I just checked with my colleague who is still running the 7.2 client and he doesn't get the error executing the query from the CLI.
I found known errors and workarounds on IBM's site regarding connecting to a 7.x database from an 8.x client. Applying those workarounds got me this far. I just wonder if I am at the end of the road 
dazedorconfused wrote:
Well, I may be SOL. I upgraded (uninstalled old, installed new) from the DB2 7.2 runtime client to the 8.x client lite that is linked to from this site before I started on this post.
I just checked with my colleague who is still running the 7.2 client and he doesn't get the error executing the query from the CLI.
Hmm...
I found known errors and workarounds on IBM's site regarding connecting to a 7.x database from an 8.x client. Applying those workarounds got me this far. I just wonder if I am at the end of the road
If you have blob fields in your database and you need them in your application, then yes I can't help you further. If not, I can update the driver for you to exclude the blob field. Though as you have an AIX machine there, it's likely you have a support contract with IBM as well. Could you ask IBM about this? The 8.x client should work, as you need .NET support.
(edit) I've updated the driver so it will only obtain the field it needs from the sequences table. Please let me know if this gets you any further. The debug build is attached to this message.
What if I were to drastically change direction and try to write a db2 driver that worked with the 7.1 client. Would that be possible? Unfortunately, we have no support for 7.1 and apparently no plans of upgrading
I currently use oledb to connect to db2.
Dim objConn As OleDbConnection
New OleDbConnection("Provider=IBMDADB2;Database=FLPRCDEX; HOSTNAME=someserver.cooperlighting.com;PROTOCOL=TCPIP;PORT=50004;uid=someuser;pwd=apassword;")
dazedorconfused wrote:
What if I were to drastically change direction and try to write a db2 driver that worked with the 7.1 client. Would that be possible? Unfortunately, we have no support for 7.1 and apparently no plans of upgrading
That's not the issue. The issue is the driver using elements from the DB2 installation you can't access or which fail to work with your client code.
That system view is required to get the column meta-data and I can't find information that this view wasn't present in 7.x.
I currently use oledb to connect to db2.
Dim objConn As OleDbConnection New OleDbConnection("Provider=IBMDADB2;Database=FLPRCDEX; HOSTNAME=someserver.cooperlighting.com;PROTOCOL=TCPIP;PORT=50004;uid=someuser;pwd=apassword;")
My idea was that it's about the user, if the userid you're using has access to SYSIBM.COLUMNS or not.
SYSIBM.COLUMNS is a view which is system wide, SYSCAT are views on the catalog level.
I re-checked and I can use these too. I'll rework the driver to use the SYSCAT schema instead. Stay tuned...
Hmm. SYSCAT.COLUMNS doesn't have all the information. Length for example is in bytes, always, also for numeric types There's only a column for SCALE, not for precision.
Hmm... I can't get the precision info..
A smallint is of length '2' in SYSCAT.COLUMNS, but has for example precision '5' in SYSIBM.COLUMNS.
Could you confirm that in your 7.x db SYSIBM.COLUMNS doesn't exist? (so it's not an access right issue) ?
(edit). Apparently, according to IBM's online docs, for for example DB2 v9, SYSCAT.COLUMNS is the way to go, not SYSIBM.COLUMNS which is nowhere to be found in the docs.
I can't find any other SYSCAT view which contains the precision info, though it should be somewhere, the SYSIBM.COLUMNS does contain the precision info
(edit) ah, I can alter SYSIBM.COLUMNS, so I can see where it gets that funky NUMERIC_PRECISION column value 
(edit) haha LOL It's just a silly table:
CASE C.COLTYPE WHEN 'INTEGER' THEN 10 WHEN 'BIGINT' THEN 19 WHEN 'SMALLINT' THEN 5 WHEN 'REAL' THEN 24 WHEN 'DOUBLE' THEN 53 WHEN 'DECIMAL' THEN CAST(C.LENGTH AS INTEGER) ELSE CAST(NULL AS INTEGER) END
So 'precision' is artificial here, at least for non-DECIMAL fields. Ok, I can work with SYSCAT.COLUMNS now, and also the SYSCAT.COLIDENTATTRIBUTES.
It will be some work though, so I can't finish it today, I hope to have a better driver for you monday morning.
Thanks, finally, a reason to look forward to Monday 
Please see the attached driver dll. This should fix the problems you had. I simply reworked the code so it now always uses SYSCAT.COLUMNS.
It's a bizarre set of views and tables what DB2 has for meta-data. It's very inconsistent, but alas, it now works. please let me know if you now run into another roadblock.
Huh... 
That's a simple SELECT * FROM SYSCAT.COLUMNS...
Pos 35 is the 'Text' field, I can exclude that one (it's a CLOB).
I'll re-form the query, just a sec.
Hmm... that's a system view which makes it very easy to obtain PK info. There's no other way than to consult system tables and join a lot. Perhaps I can use the view's query to obtain the PK info..
It's a bit hard to determine when a given view is introduced with DB2, so that's why you run into these things. Although it's still a mistery to me why you can't use LOB fields with the .NET client.
Anyway, I'll try to pull the info from the systables using the view's query. This is still trial/error, so I hope to have a working driver tomorrow for you.
What's worse is that the FK retrieval uses a similar view: SYSIBM.SQLFOREIGNKEYS which I pressume is also not present.