Thursday, December 3, 2009
XMLSERIALIZE (XML to CLOB)
SELECT XMLSERIALIZE( X.XML_FIELD AS CLOB(80) EXCLUDING XMLDECLARATION)
FROM YY.TABLE X
FROM YY.TABLE X
Tuesday, December 1, 2009
Read DB2 ZOs repository objects
SELECT * FROM
(SELECT t.CREATOR A, t.TYPE T, t.NAME N
FROM SYSIBM.SYSTABLES t
WHERE t.type in ('T','V')
union all
SELECT t.schema, t.SEQTYPE, t.NAME
FROM SYSIBM.SYSSEQUENCES t
WHERE t.SEQTYPE in ('S')
union all
SELECT t.OWNER, 'PCK', t.NAME
FROM sysibm.syspackage t
union all
SELECT distinct t.SCHEMA, t.ROUTINETYPE, t.NAME
FROM SYSIBM.SYSPARMS t
WHERE t.ROUTINETYPE in ('P','F')
) dd
order by 1, 2, 3
;
(SELECT t.CREATOR A, t.TYPE T, t.NAME N
FROM SYSIBM.SYSTABLES t
WHERE t.type in ('T','V')
union all
SELECT t.schema, t.SEQTYPE, t.NAME
FROM SYSIBM.SYSSEQUENCES t
WHERE t.SEQTYPE in ('S')
union all
SELECT t.OWNER, 'PCK', t.NAME
FROM sysibm.syspackage t
union all
SELECT distinct t.SCHEMA, t.ROUTINETYPE, t.NAME
FROM SYSIBM.SYSPARMS t
WHERE t.ROUTINETYPE in ('P','F')
) dd
order by 1, 2, 3
;
DUAL ORACLE = SYSIBM.SYSDUMMY1 IBM
Table vith one row and one columen in DB2 is: SYSIBM.SYSDUMMY1 (in Oracle DUAL).
SEQUENCE NEXT value for DB2
DB2 ZOs RACF group privileges
You have users in GROUP1 and GROUP2. In DB2 you have SELECT privileges on objects to GROUP1 and some administration (create sequence) privileges to GROUP2. If you would like to specify witch group you will use on create you mus do the folowing:
SET CURRENT SQLID = 'GROUP2';
SET CURRENT SQLID = 'GROUP2';
XMLPARSE and XMLTABLE sample for dynamic SQL (CLOB to XML to SQL)
SELECT T.*
FROM (select xmlparse(document '
<y>
<patient id="11123">
<name>Sara Lee</name>
<addr>
<street>33 Maple</street>
<city>Nearly</city>
<state>NJ</state>
<zip>07123</zip>
</addr>
<phone type="home">908-842-7531</phone>
<email>saralee@cakemail.com</email>
<service>
<sdate>2004-02-29</sdate>
<reason>brittle nails</reason>
<descrip>prescribe hormones</descrip>
<cost>84.00</cost>
<copay>15.00</copay>
</service>
</patient>
<patient id="55555">
<name>Jim Beam</name>
<addr>
<street>789 Pine</street>
<city>Uptown</city>
<state>NJ</state>
<zip>07733</zip>
</addr>
<phone type="home">908-554-5454</phone>
<phone type="work">908-445-4545</phone>
<phone type="fax">908-332-2424</phone>
<email>jbeam@gmail.com</email>
<service>
<sdate>2007-10-31</sdate>
<reason>short of breath</reason>
<descrip>trick or treatment</descrip>
<cost>45.00</cost>
<copay>5.00</copay>
</service>
</patient>
</y>
' strip whitespace) as PATIENT_XML
from sysibm.sysdummy1) P, XMLTABLE (XMLNAMESPACES(DEFAULT ''),
'/y/patient' PASSING P.PATIENT_XML
COLUMNS "PATNAME" VARCHAR(30) PATH 'name',
"LOCATION" VARCHAR(30) PATH 'addr/city',
"ID" VARCHAR(30) PATH '@id'
) T
;
Sample is formed from presentation of Gregg Lippa (Gregg Lippa-Using XML with DB2).
