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

#!/bin/ksh
. /home/db2inst1/sqllib/db2profile
db2 connect to tecdb
db2 -x "CALL SYSPROC.REORGCHK_TB_STATS('T', 'ALL')" | grep '*' | awk '{print $2 }'|while read TABLES
do
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"
done
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.
Thanks
- Rosemary.



#!/bin/ksh
. /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.

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 assignmentIf that doesn't work redesign the AWK command of your second try like this: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:With this you'll store the dynamicly build string in variable stmnt and finally execute that statement as a system command.

Good luck,
Hans

Hans,

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

Rosemary