Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007

    Unanswered: Hard time getting this script to work. Help pls

    My goal is to get a list of tables from the SYSPROC.REORGCHK_TB_STATS. I will then pass the resulting tables as an input into a second select query

    First I tried this:

    TRY 1

    . /home/db2inst1/sqllib/db2profile
    db2 connect to tecdb
    db2 -x "CALL SYSPROC.REORGCHK_TB_STATS('T', 'ALL')" | grep '*' | awk '{print $2 }'|while read TABLES
    db2 "select substr(A.tabschema,1,10)schema,substr(A.tabname,1, 50)tabname,A.npages,substr(B.tbspace,1,30)tablespa
    ce,B.pagesize,C.indextype from syscat.tables A,syscat.tablespaces B,syscat.indexes C where A.tabname=$TABLES and
    C.indextype='CLUS' and A.tbspaceid = B.tbspaceid and A.tabname = C.tabname order by 3 desc"
    db2 connect reset
    This is the error I get: SQL0206N "<tablename>" is not valid in the context where it is used-----------------------------------------------------
    After several unsuccessfull attempts I gave up. Probably the logic is not right.

    TRY 2

    I then tried to generate a select statement for each of the tables in the tables list initially generated. Here too, I can't seem to get the syntax right. Either I get a unmatched '( or nothing is returned. You can tell I am not good at scripting. Just trying very hard to learn. Any help will be greatly appreciated.
    - Rosemary.

    . /home/db2inst1/sqllib/db2profile

    db2 -x "CALL SYSPROC.REORGCHK_TB_STATS('T', 'ALL')" | grep '*' |tee sysreorg.seed

    cat sysreorg.seed|awk '{print "db2 select A"".""schema,"" ""A"."tabname,"" ""A"."npages,"" ""B"."pagesize,""""C"
    ."indextype from syscat"".""tables A,""""syscat"".""tablespaces B,""""syscat"."indexes C"" ""where A"."tabname""
    " "=" \'$1\' """ "and C"."indextype""=\'CLUS\' " "" "and A"."tbspaceid" "=" "B"".""tbspaceid and A"".""tabname"
    "=" "C"".""tabname order by 3 desc"}'

    --- either the logic is not good or the "" dont match and I cannot fix it. I have spent like 4hrs on this.
    I will appreciate any other way to achieve my goal.

    Thanks again.

  2. #2
    Join Date
    Aug 2006
    The Netherlands
    Hi, I'm not realy familiar with DB2 but I see some things that might go wrong. First try your first attempt preceding with this assignment
    . /home/db2inst1/sqllib/db2profile
    export TABLES
    db2 connect to tecdb
    If that doesn't work redesign the AWK command of your second try like this:
    awk '{
    	printf("%s \"%s %s %s %s%s.%s%s %s %s%s%s %s %s;\"\n",
    		"db2", "select A.schema, A.tabname, A.npages, B.pagesize,",
    		"C.indextype from syscat.tables A, syscat.tablespaces B,",
    		"syscat.indexes C where A.tabname =", quote, $1, $2, quote,
    		"and C.indextype =", quote, "CLUS", quote,
    		"and A.tbspaceid = B.tbspaceid",
    		"and A.tabname = C.tabname order by 3 desc")
    	}' quote="'" `db2 -x "CALL SYSPROC.REORGCHK_TB_STATS('T', 'ALL')" | grep '*'`
    The single quote can't be used within the AWK code because the interpreter regards that as source terminator. That's why it's done here in a roundabout way by assigning the ' to a variable (quote) before the value of the db2 command is read. Printf is the formatting version of print and gives much more flexibility as you can see, it's the equivalent of the C printf function. If you're not acquainted with it: the first argument is the formatstring, which consists of placeholders for stringvariables (%s) and spaces only. The single dot in it is the dot between the table_schema and table_name. The other arguments are the subsequent literals/variables ($1 and $2 refer to the first and second field of the input record) that are substituted by the placeholders. See the man page for further information.

    If that's printing the correct SQL statements (don't know what kind of UNIX system and therefore what kind of AWK you're using) complete the AWK program in a way it will execute the SQL statements as well. You can do that like:
    awk '{	stmnt = sprintf("%s \"%s %s %s %s%s.%s%s %s %s%s%s %s %s;\",
    	}' quote="'" ...
    With this you'll store the dynamicly build string in variable stmnt and finally execute that statement as a system command.

    Good luck,

  3. #3
    Join Date
    Oct 2007

    Thanks a million. I was able to edit it along the lines of what you gave me and it worked.


  4. #4
    Join Date
    Dec 2002
    Madrid - Spain
    Provided Answers: 1
    Hi Rosemary ,
    you can send me the commands complete for to do this task?

    Thank you for advanced.
    DBA DB2 for LUW

Posting Permissions

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