Hi all ,

i got a script from the internet which filters the tables via calling stored procedure reorgchk and list tables which needs to be reorg

db2 reorgchk current statistics on table all | awk '
/^Table:/ { TAB = $2 }
$NF ~ /\*+/ { printf("%s\n", TAB) }' | sort | uniq

but i have seen this is not filtering the list of tables where it finds * sign ..

can somebody help me with it or provide better solution for it .

Thanks in advance

What DB2 version and OS are you using?

Andy

db2 level is DB2 code release "SQL09015"
O/S is SunOS

You can do it with straight SQL:

Ignore the result set from the call of the stored procedure. The call is just to setup the temp table:

call sysproc.reorgchk_tb_stats('T','ALL')
select * from session.tb_stats where reorg <> '---'

Andy

but this will be only for the current session only. and i capture where it find * it list the name of that table .

i got this error in the end
+ db2 select 1 exa local.cshrc local.login local.profile migration.log reorgchkrpt sqllib sqllib_v81 from session.tb_stats where reorg
+ 0<> ---
SQL0104N An unexpected token "1 exa local.cshrc local.login local.profile
migration." was found following "select ". Expected tokens may include:
"<space>". SQLSTATE=42601
$

If you are going to do a select statement like that you will need to do it like this:

db2 "select ... from ... where..."

You need to use the quotes, otherwise the OS will try to interpret some of it and it will usually fail.

Also you columns for the select are not in the table, what are they? (exa local.cshrc local.login local.profile migration.log reorgchkrpt sqllib sqllib_v81)


Andy

i am not comfortable with this ..as it is not filtering out tables which needs reorg ..can somebody help me ..

Do it this way. Put the following into a DB2 script file (fix where appropriate):

connect to mydb;
call sysproc.reorgchk_tb_stats('T','ALL');
export to /mydir/reorg_tables.csv of del select * from session.tb_stats where reorg <> '---';
disconnect all

The all you will need to do is execute this and the tables that need reorg will be in the file:

db2 -tvsf reorg_script.sql


Andy

Hi andy,

thanks for your help its working for me ..the result set is like this ."MONSCHEMA","T_LOGSPACE_MON",7797,0,114,114,-1,0,0,0,100,"-*-"
"MONSCHEMA","T_TBSPACE_MON",127761,0,3654,5848 ,-1,0,0,0,62,"-**"
"UDBADM ","AUDIT_SUMMARY",0,0,0,374,-1,0,0,0,0,"-**"
"UDBADM ","CALL_EVNT_SOR_DATA_OLD",0,0,0,161447,-1,0,0,0,0,"-**"
"UDBADM ","HOLD_WP",0,0,0,444,-1,0,0,0,0,"-**"
"UDBADM ","SSO_TOKEN",0,0,0,96,-1,0,0,0,0,"-**"
"UDBBDE ","ATTR",176000,0,798,798,-1,0,0,0,100,"-*-"
"UDBBDE ","DRH",0,0,0,2975,-1,0,0,0,0,"-**"
"UDBBDE ","FCL_DEL",87000,0,858,3886,-1,0,0,0,22,"-**"
"UDBBDE ","HOLD_CR_TKT",0,0,0,1112,-1,0,0,0,0,"-**"
"UDBBDE ","HOLD_MRTG_DEL",0,0,0,2023,-1,0,0,0,0,"-**"
"UDBBDE ","HOLD_WRKOT",73113,0,685,855,-1,0,0,0,80,"-**"
"UDBBDE ","HOLD_WRKOT_ALL",184000,0,1705,2355,-1,0,0,0,72,"-**"
"UDBBDE ","HWP",109734,0,3328,3328,-1,0,0,0,100,"-*-"
"UDBBDE ","HWP2",4000,0,65,5201,-1,0,0,0,1,"-**"
~


now the probem is i want only the table name .not every thing

Then change the "Select *" to select whatever columns you want.

Andy