Page 1 of 2 12 LastLast
Results 1 to 15 of 24

Thread: Script Library

  1. #1
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Unanswered: Script Library

    During the course of discussions, a number of scripts are posted in the forum. Most of them are generic ones that will be useful to other folk. This thread is an attempt to build a library of such useful scripts.
    Please post scripts that you are interested to share with the rest of the community in this thread. The script may be - a UDF, a Stored Proc, a piece of SQL, an Operating system script etc.
    A few guidelines :
    a) Always inculude a description of the script
    b) Where appropriate, give a template of how to call the script.
    c) If you have already posted to this forum, you may include the link to the thread.
    d) If a script exists in the library, use the link when you respond to a post in the forum
    e) Feel free to give links of scripts in external websites.
    f) Use the code smart tags.
    g) Post only generic ones in this thread. A script specific to a particular discussion in a thread is better off in that thread.
    h) Do not 'discuss' or exchange ideas in this thread. Such posts will be removed. If you have a better version of a script posted by another user, post the improved one. If you find a bug in the code, use 'Send a Personal Message' to alert the user.


    Thanks for contributing
    Sathyaram
    Last edited by sathyaram_s; 01-15-09 at 11:16.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    Execute SQLs from files only with one batch file - DB2 on Windows

    On general there must be two batch files (or one batch and one SQL file) to execute db2 commands on Windows from batch operation. I have written a script that only one batch is needed. This is specially useful if scheduling is required. So only one batch file instead of two.

    Code and detailed description is available in thread dos scripits for db2. Please feel free to post any commands in mentioned thread.

    Code tested on: DB2 v8.2 fp9 on Windows XP SP2
    Hope this code helps,
    Grofaty

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Generate SELECT <all column names> from <tablename>

    Unless you are doing some ad-hoc query, it is a good practice to use 'SELECT <column names> from <tablename>' instead of SELECT * FROM <tablename>

    Here's an example of how you can generate a select statement with no (well, very little ) effort

    http://www.dbforums.com/showthread.php?t=1616659


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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Query to give sizes of tables and indexes with rollup totals of table, schema, and DB

    I came up with this query to give me the sizes of table objects and associated index objects. It also totals table sizes (table + indexes), schema, and DB sizes. Not it only does table and indexes. It bases it calculations on the fpages column which is filled in by RUNSTATS. This means you need to have current RUNSTATS on all tables for this to give an accurate result.

    Note: this only does tables and indexes. It does not include other objects like Stored Procedures or packages.

    You can export to DEL (CSV) so the result can be viewed in a spreadsheet.

    with t1 (object_type_code,object_type,schema,object,tabsch ema,tabname,stats_time,card,pagesize,fpages) as
    (select 1,'Table',t.tabschema,t.tabname,t.tabschema,t.tabn ame,t.stats_time,t.card,ts.pagesize,double(t.fpage s) from syscat.tables as t inner join syscat.tablespaces as ts on (t.tbspace = ts.tbspace) where t.type in ('T','S','H','U')
    ) ,
    t2 (object_type_code,object_type,schema,object,tabsch ema,tabname,stats_time,card,pagesize,fpages) as
    ( select 2,'Index',i.indschema,i.indname,i.tabschema,i.tabn ame,i.stats_time,cast(null as bigint),ts.pagesize,double(i.nleaf) from syscat.indexes as i inner join syscat.tablespaces as ts on (i.tbspaceid = ts.tbspaceid)
    ) ,
    t3 (object_type_code,object_type,schema,object,tabsch ema,tabname,stats_time,card,pagesize,fpages) as
    ( select * from t1
    union all
    select * from t2
    ),
    t4 (object_type_code,object_type,schema,object,tabsch ema,tabname,stats_time,card,pagesize,fpages,bytes) as
    ( select t3.*,case when fpages < 0 then 0 else fpages*pagesize end as bytes from t3
    ) ,
    t4a (object_type_code,object_type,schema,object,tabsch ema,tabname,stats_time,card,pagesize,fpages,bytes) as
    ( select * from t4
    union all
    select 3,'Total',tabschema,tabname,tabschema,tabname,cast (null as timestamp),cast(null as bigint),cast(null as int),cast(null as double),sum(bytes) from t4
    group by (3,'Total',tabschema,tabname,tabschema,tabname,cas t(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double))
    union all
    select 4,'Schema Total',cast(null as varchar(128)),cast(null as varchar(128)),tabschema,cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double),sum(bytes) from t4
    group by (4,'Schema Total',cast(null as varchar(128)),cast(null as varchar(128)),tabschema,cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double))
    union all
    select 5,'Database Total',cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double),sum(bytes) from t4
    group by (5,'Database Total',cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double))

    )select t4a.*,bytes/1024 as KB,(bytes/1024)/1024 as MB,((bytes/1024)/1024)/1024 as GB from t4a
    order by tabschema,tabname,object_type_code,object ;


    Andy

  5. #5
    Join Date
    Jul 2007
    Posts
    7
    Andy, check your query.
    SQL0104N An unexpected token "ema" was found following "schema,object,tabsch". Expected tokens may include: "<space> ".

    Explanation:

    A syntax error in the SQL statement or the input command string
    for the SYSPROC.ADMIN_CMD procedure was detected at the specified
    token following the text "<text>". The "<text>" field indicates
    the 20 characters of the SQL statement or the input command
    string for the SYSPROC.ADMIN_CMD procedure that preceded the
    token that is not valid.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The query is OK. It looks like some extranneous spaces got inserted when it was pasted to the post.

    I will try again:

    with t1 (object_type_code,object_type,schema,object,tabsch ema,tabname,stats_time,card,pagesize,fpages) as
    (select 1,'Table',t.tabschema,t.tabname,t.tabschema,t.tabn ame,t.stats_time,t.card,ts.pagesize,double(t.fpage s) from syscat.tables as t inner join syscat.tablespaces as ts on (t.tbspace = ts.tbspace) where t.type in ('T','S','H','U')
    ) ,
    t2 (object_type_code,object_type,schema,object,tabsch ema,tabname,stats_time,card,pagesize,fpages) as
    ( select 2,'Index',i.indschema,i.indname,i.tabschema,i.tabn ame,i.stats_time,cast(null as bigint),ts.pagesize,double(i.nleaf) from syscat.indexes as i inner join syscat.tablespaces as ts on (i.tbspaceid = ts.tbspaceid)
    ) ,
    t3 (object_type_code,object_type,schema,object,tabsch ema,tabname,stats_time,card,pagesize,fpages) as
    ( select * from t1
    union all
    select * from t2
    ),
    t4 (object_type_code,object_type,schema,object,tabsch ema,tabname,stats_time,card,pagesize,fpages,bytes) as
    ( select t3.*,case when fpages < 0 then 0 else fpages*pagesize end as bytes from t3
    ) ,
    t4a (object_type_code,object_type,schema,object,tabsch ema,tabname,stats_time,card,pagesize,fpages,bytes) as
    ( select * from t4
    union all
    select 3,'Total',tabschema,tabname,tabschema,tabname,cast (null as timestamp),cast(null as bigint),cast(null as int),cast(null as double),sum(bytes) from t4
    group by (3,'Total',tabschema,tabname,tabschema,tabname,cas t(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double))
    union all
    select 4,'Schema Total',cast(null as varchar(128)),cast(null as varchar(128)),tabschema,cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double),sum(bytes) from t4
    group by (4,'Schema Total',cast(null as varchar(128)),cast(null as varchar(128)),tabschema,cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double))
    union all
    select 5,'Database Total',cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double),sum(bytes) from t4
    group by (5,'Database Total',cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as varchar(128)),cast(null as timestamp),cast(null as bigint),cast(null as int),cast(null as double))

    )select t4a.*,bytes/1024 as KB,(bytes/1024)/1024 as MB,((bytes/1024)/1024)/1024 as GB from t4a
    order by tabschema,tabname,object_type_code,object ;


    Andy

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It looks like it is still doing it.

    Andy

  8. #8
    Join Date
    Jul 2007
    Posts
    7
    Yeah, the sql in the email response is good. Maybe try an attachment?

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Here it is attached.

    Andy
    Attached Files Attached Files

  10. #10
    Join Date
    Nov 2007
    Posts
    72

    reorgchk and reorg

    $ db2 "CALL REORGCHK_TB_STATS('T','ALL')" | grep '*' | awk '{print "REORG TABLE " $1 "." $2 ";\n" "RUNSTATS ON TABLE " $1 "." $2 ";"}'


    this is the first time i am putting some input from my side hope it help others .

  11. #11
    Join Date
    Nov 2007
    Posts
    72

    tablespace sizes

    here is the script that can help you to see the size of tablespaces and how much free space is left .its written in perl
    syntax-perl tbs_usage_perl <database name> and it will create a file called tbsusage .
    i had renamed the file coz it was not allowing me to load a file with .pl extension rename it again and remove .txt from the end.
    Attached Files Attached Files

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126

    aggregate concatenation

    The query below will concatenate (in alphabetic order) the fields in column "v", grouped by the column "g".
    This can be seen as a kind of "table transpose", or as a new grouping (or aggregation) function, next to COUNT, SUM, AVG, MIN and MAX.

    Just to help understand the query: the table
    Code:
    items.g items.v
    10      a
    10      b
    20      b
    20      c
    20      d
    will be transformed into
    Code:
    g  list
    10 a, b
    20 b, c, d
    Code:
    WITH t(g, list) AS
    ( SELECT g, CAST(RTRIM(v) AS VARCHAR(254))
      FROM   items
     UNION ALL
      SELECT items.g, t.list || ', ' ||RTRIM(items.v)
      FROM   t INNER JOIN items ON t.g = items.g
      WHERE  LOCATE(RTRIM(items.v),t.list) = 0 )
    , l(g, len) AS
    ( SELECT g, MAX(LENGTH(list))
      FROM   t
      GROUP BY g )
    SELECT t.g, MIN(t.list)
    FROM   t INNER JOIN l  ON  l.g = t.g AND l.len = LENGTH(t.list)
    GROUP BY t.g
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126

    [z/OS] List the most recent REORGs

    Reverse chronological list of all REORGs on the system:
    Code:
    SELECT substr(rtrim(dsname) ||
           coalesce('['||cast(nullif(dsnum, 0) AS char(3))||']','')
           , 1, 22) AS tablespace,
           CASE ictype WHEN 'W' THEN 'log(NO)'
                       WHEN 'X' THEN 'log(YES)'
                       ELSE          ictype END,
           substr(CAST(timestamp AS char(26)), 1, 19) AS timestamp,
           jobname,
           authid
    FROM   sysibm.syscopy
    WHERE  ictype IN ('W','X')
    ORDER BY start_rba DESC
    The following can be added to the WHERE condition, if wanted:
    AND dbname = 'DATABASENAME' (use an existing name)
    AND tsname = 'TSNAME'
    AND timestamp > current timestamp - 7 days (or similar)
    AND authid LIKE 'UID pattern'
    ...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  14. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Here is a variant on the above, to concatenate the fields in column "v", grouped by the column "g", but in the order specified by column "s".

    The table
    Code:
    items.g items.v items.s
    10      a       2
    10      b       1
    20      b       7
    20      c       700
    20      d       70
    will be transformed into
    Code:
    g  list
    10 b, a
    20 b, d, c
    Code:
    WITH t(g, s, list) AS
    ( SELECT g, s, CAST(RTRIM(v) AS VARCHAR(254))
      FROM   items
     UNION ALL
      SELECT t.g, items.s, t.list || ', ' ||RTRIM(items.v)
      FROM   t INNER JOIN items i ON t.g = i.g
      WHERE  LOCATE(RTRIM(items.v),t.list) = 0
        AND  i.s > t.s
        AND  NOT EXISTS (SELECT 1 FROM items
                         WHERE  g = i.g
                           AND  s > i.s
                           AND  s < t.s)
    )
    , l(g, len) AS
    ( SELECT g, MAX(LENGTH(list))
      FROM   t
      GROUP BY g )
    SELECT t.g, t.list
    FROM   t INNER JOIN l  ON  l.g = t.g AND l.len = LENGTH(t.list)
    or (maybe slightly more performant):
    Code:
    WITH t(g, s, list) AS
    ( SELECT g, s, CAST(RTRIM(v) AS VARCHAR(254))
      FROM   items
     UNION ALL
      SELECT t.g, items.s, t.list || ', ' ||RTRIM(items.v)
      FROM   t INNER JOIN items i ON t.g = i.g
      WHERE  LOCATE(RTRIM(items.v),t.list) = 0
        AND  i.s = (SELECT MIN(s) FROM items
                    WHERE  g = i.g
                      AND  s > t.s)
    )
    , l(g, len) AS
    ( SELECT g, MAX(LENGTH(list))
      FROM   t
      GROUP BY g )
    SELECT t.g, t.list
    FROM   t INNER JOIN l  ON  l.g = t.g AND l.len = LENGTH(t.list)
    Last edited by Peter.Vanroose; 09-29-08 at 05:29.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  15. #15
    Join Date
    Dec 2008
    Posts
    59
    Thanks for your guidelines.
    Here is link to download the script library.
    visit and download.
    http://www.thescriptlibrary.com/

Posting Permissions

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