Products
Rob Brooks-Bilson's Blog
DB2 stores information about database objects in what it calls the System Catalog. There are a number of views in the system catalog you can use to obtain metadata about your database such as columns, key relationships, schemas, stored procs, etc.
Today, I was working on a report where I wanted to display statistics about a number of staging files we use in our applications. I wanted to list things like number of records, number of columns, column names, etc. Things like the number of columns were easy to do just by selecting count(*) from the source table. To get the number of columns, though, I didn't want to use a hack like selecting a single record and getting the column list from that. What I wanted to do was query the system catalog. Unlike SQL Server, Oracle, and just about every other DB platform out there, documentation for DB2/400 is rather scarce.
After a lot of googling, I found a DB2/400 reference that had the information on the system catalog I needed. For those interested, the full documentation set can be found here. As IBM documentation has a tendency to disappear, here's the SYSIBM.SQLCOLUMNS library/view I used to query column information from the system catalog:
<cfquery name="getColumnCount" datasource="myDSN">
SELECT count(*) as totalRows
FROM SYSIBM.SQLCOLUMNS
WHERE TABLE_NAME = 'yourTable'
</cfquery>
If you want to get a list of all columns, along with a lot of additional info about each column, just do a SELECT * instead of the count.
There are several other useful SQL views you can use:
SQLCOLPRIVILEGES, SQLFOREIGNKEYS, SQLPRIMARYKEYS, SQLPROCEDURECOLS, SQLPROCEDURES, SQLSCHEMAS, SQLSPECIALCOLUMNS, SQLSTATISTICS, SQLTABLEPRIVILEGES, SQLTABLES, SQLTYPEINFO, SQLUDTS
Aura skin for Raymond Camden's BlogCFC inspired by Joe Rinehart & Steven Erat. This blog is running version 5.9.004.
For those of you wondering how we last without primary keys, we have logicals which are basically like indexes, only a lot better. The first logical is pretty much your primary key, but it isnt registered in the system, and thus these tables like normal primary keys are.
It's funny you mention that. We're in a similar boat here, where a good chunk of our apps are still legacy, and written in RPG. I spent a few days at IBM's executive briefing center in Rochester, where they basically said that the path on the iSeries is Java/SQL. This was three years ago. Since then, I've seen IBM make a lot of progress on the SQL side, but not quite as much on the Java side.
We're at a point where we're trying to get all of our RPG developers to at least upgrade their skills to using free/free and SQL as opposed to the older RPG-ILE syntax, and native database access commands. It used to be that native access was much faster than SQL access, but that's not the case anymore.
As for leaving out primary keys, that's one of the things that's caused me (and my company) the most pain over the years. We have lots of applications/tables where the primary key is a concatenation of 6-12 fielsd. It's horridly slow for lookups. Worse, as most "legacy" applications use code to manage referential integrity, there tend to be very few constraints actually enforced at the DB level, leading to all sorts of fun issues. We've done a lot over the years to improve performance - vector indexes have been a must-have. Also, the JTOpen JDBC driver has been much much better to work with than the old Client Access ODBC drivers in the pre CFMX days.
For instance, we are still using the old client access odbc drivers. In fact I am in the middle of rolling out an application that relies on over 200 connections to that many as/400's across the country. So getting that jtopen driver to work would be a great deal for us.
The majority of applications for the company are still being developed in rpg-le with us unfortunately. There has been a little push to go to the newer free form rpg, but its still just as bad in most of the ways. And again, the file structures we have are horrible to work with with sql.
Also, do you have any white papers or any other information I could show our developers to prove the fact that sql is just as fast as native database access? I would love to prove that point!
oh, and do you know if the jtopen driver would let you access data areas?
I'm just started in a proyect that use a DB2/400 as a main database. Last year i worked in DB2/UDB for LUW, and
it works pretty fine until i started with SQL STORE PROCEDURE, that it was a pain. Naturally as a DBA i found a lot
of information about the SQL PROCEDURE IN DB2/UDB LUW.
Now i suggest you guys read the middlewares that a lot of companies provide to access data in DB2 Native.
I just let you one of those http://www.itjungle.com/mso/mso100703-story04.html...
Anyway, i have a question for you guys, do you know how can i get all the index an relations between tables
that, like you said, are no in the database??...
Any questions
write me back
grettins
Remi