Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24

Thread: Script Library

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

    "de"catenate (i.e., normalize) lists

    Sometimes, table fields contain lists (like lists of telephone numbers).
    This is a non-normalized table design, of course, but often chosen for efficiency and/or simplicity, since the normalized design would require an additional table.

    Well, it could be useful to have that normalized table available anyhow, e.g. through a VIEW on the (non-normalized) table with list entries, or through a common table expression (CTE), aka a "private view".

    In a sense, this is the opposite of "aggregate concatenation" as discussed in http://www.dbforums.com/showthread.php?p=6323330 (up in this thread).

    The query below will "de"catenate (i.e., split over multiple lines) the fields in column "v", based on the separator ",". This is done using a recursive CTE.

    Just to help understand the query: the table
    Code:
    items.g items.v
    10      a,b
    20      b,c,d
    will be transformed into
    Code:
    g  v
    10 a
    10 b
    20 b
    20 c
    20 d
    Essentially, the CTE "norm" does it all, so you may plug in the two CTEs (t and norm) in front of your query which requires the "normalized" design. Or use the full query in a CREATE VIEW.
    Code:
    WITH t(g, v, aux) AS
    (SELECT g, NULLIF('',''), v FROM items
     UNION ALL
     SELECT g,
            CASE posstr(aux,',')
            WHEN 0 THEN aux
    	WHEN NULLIF(0,0) THEN aux
            ELSE substr(aux, 1, posstr(aux,',')-1) END,
            CASE posstr(aux,',')
            WHEN 0 THEN NULLIF('','')
            ELSE substr(aux, posstr(aux,',')+1) END
     FROM   t
       WHERE aux IS NOT NULL
    ),
         norm(g, v) AS
    (SELECT g, v FROM t
     WHERE  v IS NOT NULL
    )
    SELECT * FROM norm
    (Note the "NULLIF"; alternative would have been a CAST(NULL AS VARCHAR(1)) or so.)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  2. #17
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Just generate a reorg/runstats script for all you tables:
    Code:
    select varchar 
    	(
    			'title '
    		|| 	lcase(rtrim(a.table_name))
    		||	space ( 40 - length(a.table_name)) 
    		||	'&& db2 -v reorg table '
    		|| 	rtrim(a.table_schema)
    		||	'.'
    		|| 	rtrim(a.table_name)
    		||	space ( 40 - length(a.table_name))
    		||	case c.pagesize	when 04096	then 'use tmp04 '
    					when 08192	then 'use tmp08 ' 
    					when 16384	then 'use tmp16 ' 
    							else 'use tmp32 '
    			end
    		||	'resetDictionary'
    		||	space ( 40 - length(a.table_name))  
    		||	' && db2 -v runstats on table '
    		|| 	rtrim(a.table_schema)
    		||	'.'
    		|| 	rtrim(a.table_name)
    		||	space (1)
    		||	'on all columns and indexes all' 
    	,384) as one_liner
    from sysibm.tables   		a
    join sysibm.systables 		b on b.name	= a.table_name
    join sysibm.systablespaces	c on c.tbspace	= b.tbspace
    where 		a.table_type			= ucase('base table')
    and		a.table_name not in 	('no_not_this_table'
    					,'no_not_that_table')
    order by 	a.table_schema
    	,	a.table_name	;

  3. #18
    Join Date
    Apr 2008
    Posts
    51
    More than just a script library: http://www.db2topgun.com/dbapf5
    db2topgun.com

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

    Hex to IpAddress

    UDF to convert the hexadecimal representation of ipaddress (in the application id) to decimal format

    http://www.dbforums.com/db2/1639388-...ml#post6386433
    Visit the new-look IDUG Website , register to gain access to the excellent content.

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

    Count Different Products

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

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

    Perfect Number

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

  7. #22
    Join Date
    Aug 2011
    Posts
    46

    script for determine lock wait using db2pd

    #!/bin/ksh
    db2pd -d sample -wlocks -tran -app -dyn > wait.out
    set -A tranline `cat wait.out| awk ' /Locks being waited on :/ {flag=1;next} /Transactions:/{flag=0} flag { print } ' |awk '/Lockname/ {flag=1;next} /Database/{flag=0} flag { print $1","$3","$5","$6","$7","$8","$9","$10","$11} '`
    set -A apphandle `cat wait.out| awk ' /Locks being waited on :/ {flag=1;next} /Transactions:/{flag=0} flag { print } ' |awk ' /Lockname/ {flag=1;next} /Database/{flag=0} flag { print $1} '`
    i=0
    echo "AppHandl,TranHdl,Type,Mode,Status,CoorEDU,AppName ,AuthID,AppID,AppSts,SQL"
    while [ $i -lt ${#apphandle[@]} ]
    do
    cat wait.out| awk -v aphandle="${apphandle[$i]}" '/CollectSectionActuals/ {flag=1;next} /External Connection Attributes/{flag=0} flag {if($2==aphandle) print $2,$6,$7,$8,$9,$10,$11 }' | while read line; do ; cat wait.out|awk -v status=`echo $line|awk '{print $2}'` -v var1=`echo $line|awk '{print $3}'` -v var2=`echo $line|awk '{print $4}'` -v var3=`echo $line|awk '{print $5}'` -v var4=`echo $line|awk '{print $6}'` -v IP=`echo $line|awk '{print $7}'` -v tr=${tranline[$i]} ' /NumExe/ {flag=1;next} /Dynamic SQL Environments:/{flag=0} flag {if($2==var1 && $3==var2) print tr","status",""Current SQL:",substr($0, index($0,$8)); else if($2==var3 && $3==var4) print tr","status",""Last SQL:",substr($0, index($0,$8)); }' ; done;
    (( i=i+1 ))
    done

  8. #23
    Join Date
    Sep 2012
    Posts
    6
    have looked in the scripts but can't find the script. sorry, but help me find one.

  9. #24
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    I see many scripts here (or links to them) - why can you not find one?

    Your question is crypric at best . . . We have no idea what kind of script would be a "find" fod you. Please clarify.

Posting Permissions

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