- 1 Preface
- 2 Query an IBM DB2 Database directly from Microsoft Access
- 3 Query Properties
- 4 DB2 Temporary Tables
- 5 Create index on Temporary Table
- 6 Get a list of all the tables in a DB2 database
- 7 Get a list of all the fields in all the tables of a DB2 database
- 8 Convert an 8-digit YYYYMMDD date into a date field
- 9 Zero-fill an integer in a query
- 10 Concatenate strings in aggregate
- 11 Convert Date into ISO String
- 12 Select the first row in every group
- 13 Determine which fields are indexed in a table
- 14 Notes
- 15 Helpful Links
Note that it is considered bad practice to use “SELECT *” in production code. However it’s done extensively on this site for illustration purposes.
Query an IBM DB2 Database directly from Microsoft Access
IBM DB2 Databases often reside on a mainframe (i.e. MVS, OS/390) system.
You can query mainframe-based databases in MS-Access via an IBM DB2 connection, using SQL pass-through queries. You should have the IBM DB2 Client Database software installed on your Windows system.
Here are instructions for MS-Access 2003. Other versions of Access should be similar.
- In your Access database window, go to Queries
Select Insert->Query in the menu
- Choose Design View and click OK
Select Query->SQL Specific->Pass-Through in the menu
If the “Query Properties” window isn’t showing, choose View->Properties from the menu
- Put your cursor in the “ODBC Connect Str” box, and click the “…” button to the right
- Choose the “Machine Data Source” tab, and find the Data Source Name of your DB2 database connection. It will probably start with the letters “DB2”.
- You’ll be asked to log on to the DB2 database. Enter your username and password and click OK.
- If you want to save your password in the connection string, you can do so by clicking “Yes”. This prevents you from having to re-enter it each time you run a DB2 query from this Access database.
- Type your SQL query in the Query window, save it, and use it like any other Access query
- I like to take the DB2 pass-through query I’ve created and use a second “Make Table” query to copy the DB2 results into an Access table. I do this because I strongly suspect that using the DB2 pass-through query itself in other subsequent queries is probably not very efficient, and it could probably cause undue strain on the server– especially if it’s used in many different other queries.
The best way to get your data into the Access database is to create a pass-through query that returns records, and create a separate Access “Make Table” query (SELECT INTO), that uses the pass-through to create a new table.
If you want a pass-through query to return data, in the “Query Properties” window, select “Yes” under “Returns Records”. If your query does not return records, select “No”. As an example, your query may create a temporary table instead of returning data.
DB2 Temporary Tables
If you want to create a temporary table that survives only for a single SQL query, encapsulate the SELECT statement within a TABLE() command:
SELECT pub_name, author, title, c_price FROM book bk, TABLE(SELECT pub_name, MAX(c_price) AS maxprice FROM book group by pub_name) AS temp1 t WHERE bk.pub_name = t.pub_name AND bk.c_price = t.maxprice
Example of creating a table with the same fields as a table called DB2TABLE:
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TABLE_NAME AS ( SELECT * FROM DB2DATABASE.DB2TABLE ) WITH NO DATA ON COMMIT PRESERVE ROWS;
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_TABLE_NAME ( Field1 varchar(5), Field2 varchar(10), "A field with a long name" integer ) ON COMMIT PRESERVE ROWS
That would create an empty table. If you want to add all the data from the table DB2TABLE after creating that table, you would add:
INSERT INTO SESSION.TEMP_TABLE_NAME SELECT * FROM DB2DATABASE.DB2TABLE;
Create index on Temporary Table
Example of creating a Ascending index on the field SSN of the TEMP_TABLE_NAME table:
CREATE INDEX SESSION.IDX_TEMP_SSN ON SESSION.TEMP_TABLE_NAME (SSN ASC);
Once the data is in a temporary table, create a seperate pass-through query to return the data.
SELECT * FROM SESSION.IDX_TEMP_SSN;
Save the above query with the name “Get Temp Table”. Now, create a regular (not a pass-through) MS-Access query to insert that data into an Access table. The SQL should look something like this:
SELECT * INTO [Access Results from Temp Table] FROM [Get Temp Table]
Get a list of all the tables in a DB2 database
SELECT * FROM sysibm.systables WHERE TYPE='T' ORDER BY NAME;
Get a list of all the fields in all the tables of a DB2 database
SELECT * FROM sysibm.syscolumns;
For a specific table:
SELECT * FROM sysibm.syscolumns WHERE TBNAME='TABLE_NAME_TO_LOOK_FOR';
Convert an 8-digit YYYYMMDD date into a date field
CASE WHEN n.A_DATE='00000000' THEN date ('9999-12-31') ELSE date ( INSERT(INSERT(n.A_DATE,5,0,'-'),8,0,'-') ) END As ARealDate
Zero-fill an integer in a query
This works in DB2 v8. It will zero-fill an integer (that is less than 8 digits) to 8 digits exactly.
To change the length, you should vary the number of zeros and the “8” in the RIGHT function. To be safe, just make them match: for example 12 zeros, and the number 12 to zero-fill to 12 places.
SELECT RIGHT('00000000' || cast(AN_INTEGER as varchar(10)),8) As ZEROFILLED_INT FROM DB2_DATABASE
or VARCHAR instead of CAST
SELECT RIGHT('00000000' || VARCHAR(AN_INTEGER),8) As ZEROFILLED_INT FROM DB2_DATABASE
Concatenate strings in aggregate
SELECT replace(replace(replace(replace( varchar(xml2clob(xmlagg(xmlelement(NAME a, TABLE.MY_TEXT_FIELD )))), '</A><A>', ', '),'<A>',''),'</A>',''),'<A/>','') As AGGREGATE_COMMA_DELIMITED_STRING FROM TBL TABLE WHERE ... etc ...
Convert Date into ISO String
SELECT char( current date , ISO ) FROM sysibm.sysdummy1
SELECT replace( char( current date , ISO ), '-', '') FROM sysibm.sysdummy1
Select the first row in every group
I often come across the need to select the first row in every group, by some criteria. For example:
Here I might want to select all the data associated with the highest value in “Field 2”:
I have been using a similar query in Access/SQL server for years and have been trying to figure out an equivalent in DB2 v8, because it doesn’t have a TOP clause. It turns out that you can just use FETCH FIRST 1 ROWS ONLY instead. I didn’t think this actually worked in a subquery, but it apparently does. Perhaps the DB2 I was querying prior was upgraded and I wasn’t aware.
SELECT t.* FROM (SELECT * FROM DATABASE.TABLE WHERE <OPTIONAL WHERE CLAUSE>) t WHERE t.ID = (SELECT ID FROM DATABASE.TABLE WHERE GROUP_NAME=t.GROUP_NAME AND <OPTIONAL WHERE CLAUSE> ORDER BY <ORDER CRITERIA> DESC FETCH FIRST 1 ROWS ONLY )
An important section to note is the statement GROUP_NAME=t.GROUP_NAME. Here you are specifying that you want to join the subquery by the group to the original query. For each group, you are selecting the first row that comes up. You use the “Order Criteria” to sort the rows in the subquery so it will return the correct row.
ID is a unique identifier for each row in the table.
Determine which fields are indexed in a table
SELECT NAME, TBNAME, TBCREATOR, COLNAME, COLNO, COLSEQ FROM sysibm.sysindexes i INNER JOIN sysibm.syskeys k ON i.name=k.ixname AND i.creator=k.ixcreator WHERE tbname LIKE 'TABLENAME%'
- DB2 has its’ own specific syntax, but it does support ANSI SQL, so if you know SQL you should be able to hit the ground running.
- Mainframes are known for their excellent I/O capability, not for their processing power. Try to keep your SQL queries short and sweet, without a lot of JOINs, GROUP BYs, or calculations. Try to keep your processing to a minimum and process what you can on the MS-Access side.
You can pull back specific information very quickly if you use an IN clause in your query on an indexed column. If it’s not on an indexed column, then you’re better off not limiting the data in your SQL. For example, say that the field SSN below is indexed:
SELECT * FROM DB2APPL.APPLICANTS a WHERE a.SSN IN ('123-45-6789','456-32-1234','024-68-3691')
You will be able to pull back all of the data from the APPLICANTS table very quickly based on the SSN column.