| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
12-22-08, 18:00
|
|
Registered User
|
|
Join Date: Sep 2004
Location: Belgium
Posts: 1,053
|
|
|
"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/db2/1615607-script-library-post6323330.html (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/
|
|
01-21-09, 08:13
|
|
Registered User
|
|
Join Date: Jan 2009
Location: Zoetermeer, Holland
Posts: 515
|
|
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 ;
|
|
02-03-09, 21:33
|
|
Registered User
|
|
Join Date: Apr 2008
Posts: 51
|
|
|
|
03-10-09, 07:52
|
|
Super Moderator
|
|
Join Date: Aug 2001
Location: UK
Posts: 4,354
|
|
|
Hex to IpAddress
UDF to convert the hexadecimal representation of ipaddress (in the application id) to decimal format
Application ID
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
|
|
06-11-09, 07:58
|
|
Super Moderator
|
|
Join Date: Aug 2001
Location: UK
Posts: 4,354
|
|
|
Count Different Products
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
|
|
07-07-09, 11:02
|
|
Super Moderator
|
|
Join Date: Aug 2001
Location: UK
Posts: 4,354
|
|
|
Perfect Number
__________________
Visit the new-look IDUG Website , register to gain access to the excellent content.
|
|
08-05-11, 03:32
|
|
Registered User
|
|
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
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Script Library
