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

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

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

Result of ALTER TABLE on VIEW


Sathyaram

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****eaf) 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

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.

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****eaf) 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

It looks like it is still doing it.

Andy

Yeah, the sql in the email response is good. Maybe try an attachment?

Here it is attached.

Andy

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

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.

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 tablewill be transformed into

Reverse chronological list of all REORGs on the system:
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'
...

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 tablewill be transformed intoor (maybe slightly more performant):

Thanks for your guidelines.
Here is link to download the script library.
visit and download.
http://www.thescriptlibrary.com/