Results 1 to 7 of 7

Thread: QMF help needed

  1. #1
    Join Date
    Aug 2006
    Posts
    33

    Question Unanswered: QMF help needed

    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.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Providing your versions of DB2 and QMF may help someone to help you

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2006
    Posts
    33
    Quote Originally Posted by sathyaram_s
    Providing your versions of DB2 and QMF may help someone to help you

    Sathyaram
    DB2 8.2
    QMF 8.1

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    is it an unusual combination of QMF and DB2 LUW ?

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Aug 2006
    Posts
    33
    Sorry, I think DB2 are 8.1 as well. Don't really know how to check the exact version of DB2.

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by jinsezh
    table1: version pass; created_time pass;...
    table2: version failed; created_time pass; ...
    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:
    Code:
    SELECT 'SELECT '''||tbname||': version '',count(distinct version)'||
           ' from '||rtrim(tbcreator)||'.'||tbname||';'
    FROM   sysibm.syscolumns
    WHERE  name = 'VERSION'
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Aug 2006
    Posts
    33
    Hi Peter,

    Thanks for the great idea.

    Jinse

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •