Preface

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.

1. In your Access database window, go to Queries

2. Select Insert->Query in the menu

3. Choose Design View and click OK

4. Select Query->SQL Specific->Pass-Through in the menu

5. If the “Query Properties” window isn’t showing, choose View->Properties from the menu

6. Put your cursor in the “ODBC Connect Str” box, and click the “…” button to the right
7. 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”.
8. You’ll be asked to log on to the DB2 database. Enter your username and password and click OK.
9. 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.
10. Type your SQL query in the Query window, save it, and use it like any other Access query
    1. 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.

Query Properties

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

Link to Source

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;

Or alternatively,

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

yields:

2011-10-14

Remove dashes

SELECT replace( char( current date , ISO ), '-', '')
FROM sysibm.sysdummy1

yields:

20111014

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:

Field 1	Field 2	Field 3
A	1	apple
B	3	lemon
C	2	cherry

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
                )

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%'