Results 1 to 10 of 10

Thread: rerorg

  1. #1
    Join Date
    Nov 2007
    Posts
    72

    Unanswered: rerorg

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS are you using?

    Andy

  3. #3
    Join Date
    Nov 2007
    Posts
    72
    db2 level is DB2 code release "SQL09015"
    O/S is SunOS

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Nov 2007
    Posts
    72
    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
    $

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  7. #7
    Join Date
    Nov 2007
    Posts
    72
    i am not comfortable with this ..as it is not filtering out tables which needs reorg ..can somebody help me ..

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  9. #9
    Join Date
    Nov 2007
    Posts
    72
    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

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Then change the "Select *" to select whatever columns you want.

    Andy

Posting Permissions

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