Hi there,

I have tens of tables, each of them contains some common columns: version, created_time, created_user etc, and all these columns should have the same default value respectively. So my DB looks like:

Table1: column11 column12 version created_time ...
row1 ... 0 2006/01/01
row2 ... 0 2006/01/01
Table2: column21 column22 version created_time ...
row1 ... 0 2006/01/01
row2 ... 0 2006/01/01
...

I want to have a little script to check these common columns in every table, and the script should be able to

1) passing the tablename
2) report if there are any exception

I want to read table name from sysibm.systables, and would like my output looks as follows:

table1: version pass; created_time pass;...
table2: version failed; created_time pass; ...

I wonder if anyone is kind enough to give me a brief example how to do this in QMF for z/os, or JCL or any other tools available in Mainframe.

Thank you.

Providing your versions of DB2 and QMF may help someone to help you

Sathyaram

DB2 8.2
QMF 8.1

is it an unusual combination of QMF and DB2 LUW ?

Sathyaram

Sorry, I think DB2 are 8.1 as well. Don't really know how to check the exact version of DB2.

This can be done with plain SQL, I would say. In two stages though, because you first want to go through the metadata.
So no need to use QMF for this: SPUFI would even be easier since the SPUFI output file can be used (with minor changes) as input file for the second pass (possibly in QMF then).

Here is an attempt at the first pass:This will generate SELECT statements which will, when executed themselves in SPUFI, produce lines of the form "table1: version n", where "n" is the number of different values in the "version" column of table1 (etc.)
This is of course not exactly what you want, but you get the picture...

Hope this helps.

Hi Peter,

Thanks for the great idea.

Jinse