Hello,

I am brand new to DB2 XML extender. I am using DB2 version 8 on z/OS. I have the extender installed and have enabled the server with the WLM.

What I am having a problem with is a step outlined in the XML Extender Getting Started Samples dialogue.

It is step 5 of the command files. Which alters the table sale_tab and adds column order with type of DB2.XMLVAR.

db2 -v "alter table sales_tab add order db2xml.xmlvarchar"

When I try to execute in USS, this command I get the following error.

145:/u/vcrose/db2xml/cmd $ getstart_alterTabCol.cmd
'alter table sales_tab add order db2xml.xmlvarchar'
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL "DB2XML". SOME SYMBOLS
THAT MIGHT BE LEGAL ARE: KEY
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 33 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000'
X'FFFFFFFF' X'00000021' X'00000000' SQL DIAGNOSTIC
INFORMATION
The SQL command failed. return code -104

Which means it is not allowed.

When I look in the SYSIBM.SYSROUTINES table the XMLVARCHAR UDT can be found.

Results from query

SCHEMA NAME ROUTINETYPE OWNER ORIGIN FUNCTION_TYPE
DB2XML XMLCLOB F DB2XML S S
DB2XML XMLCLOBFROMFILE F DB2XML E S
DB2XML XMLCLOBFROMFILE F DB2XML E S
DB2XML XMLFILE F DB2XML S S
DB2XML XMLFILEFROMCLOB F DB2XML E S
DB2XML XMLFILEFROMCLOB F DB2XML E S
DB2XML XMLFILEFROMVARCHAR F DB2XML E S
DB2XML XMLFILEFROMVARCHAR F DB2XML E S
DB2XML XMLVARCHAR F DB2XML S S
DB2XML XMLVARCHARFROMFILE F DB2XML E S
DB2XML XMLVARCHARFROMFILE F DB2XML E S
DB2XML XMLVARCHART F VCROSE S S

12 record(s) selected

Anyone have any ideas why this command does not work. The DB2XMl extender book for DB2 V8 z/OS is pretty lean for the installation.......

If you would like to reply to me direct it is vcrose@us.ibm.com Thank you.

try re-running the statement as
db2 -v "alter table sales_tab add column order db2xml.xmlvarchar"
or
db2 -v "alter table sales_tab add \"ORDER\" db2xml.xmlvarchar"
(or whatever escape character is appropriate). If one of these work it is because ORDER is a DB2 reserved word and the syntax analyser headed off down some 'interesting' path when it saw it. If the first works it's bacause the keyword COLUMN forced the analyser down the correct path; if the second it's because "ORDER" is a delimited-string, not a word.

James Campbell

I resubmitted my alter command after thinking about it. Using the column attribute. It was successful. I reviewed your post and it confirmed what I had already done. Thanks for the reply