| View previous topic :: View next topic |
| Author |
Message |
Shirisha09
Beginner
Joined: 27 Aug 2009
Posts: 7
Topics: 3
|
 Posted: Thu Jul 08, 2010 3:39 am Post subject: DB2 XML handlling |
 |
|
Hi , I need to develop a DB2 with XML handlling project .Can any one help me as to what are the prerequisites for this.
The skill set required mentioned is DB2XQuery/Xpath,Java,Stored procedure,DB2 Schema. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Jul 08, 2010 10:42 am Post subject: |
|
|
Shirisha09,
Depends on what you meant by DB2 XML handling project. Are you receiving an XML file and you want to upload the contents of it into a table or do you need to create an XML file from the DB2 table and send it somewhere else?
Kolusu |
|
| Back to top |
|
|
bauer
Intermediate
Joined: 10 Oct 2003
Posts: 317
Topics: 50
Location: Germany
|
| Posted: Mon Jul 12, 2010 3:55 am Post subject: |
|
|
or accessing DB2 Tables with SQL and xPath like this ?
| Code: |
SELECT Field, XMLQUERY ( ' ...........') AS ITEMS
FROM My.Table WHERE XMLEXISTS(' .......') AND ......
|
Works with Version 9.0 of DB2.
regards
bauer |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
|
| Back to top |
|
|
Shirisha09
Beginner
Joined: 27 Aug 2009
Posts: 7
Topics: 3
|
| Posted: Mon Jul 19, 2010 6:53 am Post subject: |
|
|
Thank you for the reference books
I have tried to create a table with XML data type and insert a row in DB2 version 9
but i need help as to how to embed the Xquery/XPath or XML related SQL's in COBOL.As I could not create DCLGEN using SPUFI option 2. Created a cobol equivalent for XML data type manually.
1) Table declaration:
| Code: |
CREATE TABLE CUSTOMER(ID INT
CONSTRAINT PK_ID PRIMARY KEY
NOT NULL,
NAME VARCHAR(50),
ADDR VARCHAR(300),
INFORMATION XML) IN IBMDB.IBMTS;
|
2) DCLgen with cobol equivalents:
| Code: |
01 R05CUST
10 IDNO PIC S9(9) USAGE COMP.
10 NAME.
49 NAME-LEN PIC S9(4) USAGE COMP.
49 NAME-TEXT PIC X(50).
10 ADDR.
49 ADDR-LEN PIC S9(4) USAGE COMP.
49 ADDR-TEXT PIC X(300).
10 INFORMATION PIC SQL TYPE IS XML AS CLOB.
|
3) And the variable in COBOL
| Code: |
EXEC SQL BEGIN DECLARE SECTION END-SQL.
01 WS-XDC PIC SQL TYPE IS CLOB(5K).
EXEC SQL END DECLARE SECTION END-SQL.
|
4) Embeded SQl that i used
| Code: |
EXEC SQL
SELECT
ID,
NAME,
ADDR,
XMLSERIALIZE(INFORMATION AS CLOB) AS INFO
INTO :IDNO,:NAME,:ADDR,:XDC
FROM DBADMIN.XTAB
WHERE ID=102
END-EXEC.
|
I am getting a precompile error = 12 with
| Code: |
DSNH084I W DSNHLEXC LINE 44 COL 22 UNACCEPTABLE SQL STATEMENT
DSNH004I S DSNHLEXC PROCEDURE DIVISION COULD NOT BE FOUND
|
Can any one let me know is this appraoch rite.. |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Mon Jul 19, 2010 10:55 am Post subject: |
|
|
Shirisha09,
Make sure that all your EXEC SQL statements are having an END-EXEC with a period at end. Also I suggest you add the pre-compiler option HOST(COBOL)
| Quote: |
but i need help as to how to embed the Xquery/XPath or XML related SQL's in COBOL.As I could not create DCLGEN using SPUFI option 2. Created a cobol equivalent for XML data type manually.
|
May be you don't have the defaults set up correctly. On the DB2I PRIMARY OPTION MENU . select Option
| Code: |
D DB2I DEFAULTS (Set global parameters)
|
and it should have the following values
| Code: |
1 DB2 NAME ............. ===> XXXX (Subsystem identifier)
2 DB2 CONNECTION RETRIES ===> 0 (How many retries for DB2 connection)
3 APPLICATION LANGUAGE ===> IBMCOB (ASM, C, CPP, IBMCOB, FORTRAN, PLI)
4 LINES/PAGE OF LISTING ===> 60 (A number from 5 to 999)
5 MESSAGE LEVEL ........ ===> I (Information, Warning, Error, Severe)
6 SQL STRING DELIMITER ===> DEFAULT (DEFAULT, ' or ")
7 DECIMAL POINT ........ ===> . (. or ,)
8 STOP IF RETURN CODE >= ===> 8 (Lowest terminating return code)
9 NUMBER OF ROWS ....... ===> 20 (For ISPF Tables)
10 CHANGE HELP BOOK NAMES?===> NO (YES to change HELP data set names)
|
http://www.ibm.com/support/docview.wss?uid=swg21302809
Kolusu |
|
| Back to top |
|
|
Shirisha09
Beginner
Joined: 27 Aug 2009
Posts: 7
Topics: 3
|
| Posted: Tue Jul 20, 2010 12:50 am Post subject: |
|
|
All the DB2I defaults were set with the values that you have mentioned.I could create a DCLGEN with same values but for diferent subsystem pointingto DB2 v8.But i am facing problem for V9. The error message that i get is.
DSNT408I SQLCODE = -805, ERROR: DBRM OR PACKAGE NAME DB9RLOC..DSNECP68.1-
7982A1813786044 NOT FOUND IN PLAN DSNEDCL. REASON 03
DSNT418I SQLSTATE = 51002 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXEPM SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -251 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF05' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
***
I have also added the precompiler option as HOST(COBOL) but was getting Max CC=8 with the error mesages.
DSNH024I W DSNHOPTS SUBOPTION "COBOL" INVALID FOR OPTION "HOST"
DSNH312I E DSNHSMUD LINE 66 COL 20 UNDEFINED OR UNUSABLE HOST VARIABLE "ID"
DSNH312I E DSNHSMUD LINE 66 COL 36 UNDEFINED OR UNUSABLE HOST VARIABLE "INFORMATION"
SELECT ID,NAME,ADDR,INFORMATION INTO : ID,: NAME,: ADDR,: INFORMATION FROM DBADMIN.CUSTOMER WHERE ID=102.
Can you please suggest . |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Jul 20, 2010 10:24 am Post subject: |
|
|
| Shirisha09 wrote: | | All the DB2I defaults were set with the values that you have mentioned.I could create a DCLGEN with same values but for diferent subsystem pointingto DB2 v8.But i am facing problem for V9. The error message that i get is. |
Shirisha09,
It looks like your DBV9 system isn't installed properly. Talk to your DBA
| Quote: | | I have also added the precompiler option as HOST(COBOL) but was getting Max CC=8 with the error mesages. |
Where did you add the option? did you add it as part of parm? Show the precompiler step JCL
Kolusu |
|
| Back to top |
|
|
Shirisha09
Beginner
Joined: 27 Aug 2009
Posts: 7
Topics: 3
|
| Posted: Fri Jul 23, 2010 9:28 am Post subject: |
|
|
Thank you for the details i could compile COBOL program.
Can you please let me know if any settings are required to execute XMLQUERY in SPUFI. The query that i am using to execute is
| Code: |
SELECT XMLQUERY('/PURCHASEORDERS/ITEMS/ITEM[DESC=BABY]/QUANTITY'
PASSING POXML)
FROM PURCHASEORDERS
WHERE XMLEXISTS('/PURCHASEORDERS/ITEMS/ITEM[DESC=BABY]'
PASSING POXML);;;
|
The error mesage that i get is
| Code: |
IEA995I SYMPTOM DUMP OUTPUT
SYSTEM COMPLETION CODE=04E
TIME=19.54.07 SEQ=24234 CPU=0000 ASID=005F
PSW AT TIME OF ERROR 078D1000 800ABF34 ILC 2 INTC 0D
ACTIVE LOAD MODULE ADDRESS=000A0A78 OFFSET=0000B4BC
NAME=DSNESM00
DATA AT PSW 000ABF2E - 00181610 0A0D9180 60374710
GR 0: 80000000 1: 8004E000
2: 000D3000 3: 000003DD
4: 00000000 5: 00000000
6: 00055EA6 7: 00000008
8: 00000001 9: 000D3010
A: 000AC5EF B: 0004F258
C: 800AB5F0 D: 0004F258
E: 800A8098 F: 00C50002
END OF SYMPTOM DUMP
Abend 04E000 hex occurred processing command 'DSN '.
*** |
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Fri Jul 23, 2010 10:47 am Post subject: |
|
|
Shirisha09,
04E is one of DB2's default abend code. Try the following
1. Run the query
2. Once you get the abend go to SDSF
3. Choose DA option on SDSF primary option menu.
4. You should see a job executing with name DBM* or DB2MSTR*
5. Browse that job
6. Search for your 04E or your id
7. This gives a detailed view of your error with reason code.
8. Now search this manual for the reason code.
http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNMCJ12/CCONTENTS
or the simple option is to pick up the phone and call your DBA and he will be able to diagnose it for you.
Kolusu |
|
| Back to top |
|
|
Shirisha09
Beginner
Joined: 27 Aug 2009
Posts: 7
Topics: 3
|
| Posted: Mon Jul 26, 2010 9:10 am Post subject: |
|
|
Hi,
I am not getting that error when i use XMLCAST.But i think i am missing something can you please let me know if this is rite.
The query that i am using is:
| Code: |
SELECT XMLCAST(XMLQUERY('declare default element namespace
"http://posample.org";
/customerinfo/name' PASSING INFO)
AS VARCHAR(20))
FROM MYCUSTOMER
WHERE XMLEXISTS('declare default element namespace
"http://posample.org";
/customerinfo/phone[@type="cell"]' PASSING INFO);;;;;;;
|
and the error message that i get is | Code: |
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "(". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: , )
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE |
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
