(c) Copyright IBM Corporation 1993,2002 Command Line Processor for DB2 SDK 8.1.0 You can issue database manager commands and SQL statements from the command prompt. For example: db2 => connect to sample db2 => bind sample.bnd For general help, type: ?. For command help, type: ? command, where command can be the first few keywords of a database manager command. For example: ? CATALOG DATABASE for help on the CATALOG DATABASE command ? CATALOG for help on all of the CATALOG commands. To exit db2 interactive mode, type QUIT at the command prompt. Outside interactive mode, all commands must be prefixed with 'db2'. To list the current command option settings, type LIST COMMAND OPTIONS. For more detailed help, refer to the Online Reference Manual. db2 => connect to questpug user quest using k2ngarthur Database Connection Information Database server = DB2/6000 8.1.3 SQL authorization ID = QUEST Local database alias = QUESTPUG db2 => select * from product.category where typecatind = 341148 PRODUCTIND TYPECATIND ----------- ----------- 318564 341148 1 record(s) selected. db2 => DELETE FROM PRODUCT.TYPECATEGORY WHERE TYPECATIND = 341148 AND PARENTCATIND = 341152 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0438N Application raised error with diagnostic text: "Cannot delete - product.category still has row with typecatind ". SQLSTATE=NODEL db2 => select * FROM PRODUCT.TYPECATEGORY WHERE TYPECATIND = 341148 AND PARENTCATIND = 341152 TYPECATIND DESCRIPTION PARENTCATIND CATEGORYLEVEL ORDERNUM CHILDSORT OWNER VALID TAXONOMYCODE TAXONOMYDESCRIPTION ----------- -------------------------------------------------- ------------ --------------- -------- --------- ------------ ----- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 341148 asd 341152 project 0 N QUEST Y 1 record(s) selected. db2 => select * FROM PRODUCT.TYPECATEGORY WHERE TYPECATIND = 341152 TYPECATIND DESCRIPTION PARENTCATIND CATEGORYLEVEL ORDERNUM CHILDSORT OWNER VALID TAXONOMYCODE TAXONOMYDESCRIPTION ----------- -------------------------------------------------- ------------ --------------- -------- --------- ------------ ----- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 341152 RTY 341151 model 0 N SAGARJ Y null null 1 record(s) selected. db2 => select * FROM PRODUCT.TYPECATEGORY WHERE TYPECATIND = 341151 TYPECATIND DESCRIPTION PARENTCATIND CATEGORYLEVEL ORDERNUM CHILDSORT OWNER VALID TAXONOMYCODE TAXONOMYDESCRIPTION ----------- -------------------------------------------------- ------------ --------------- -------- --------- ------------ ----- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 341151 9999 101087 machine 0 N SAGARJ Y 1 record(s) selected. db2 => select * from product.category where PRODUCTIND =318568 PRODUCTIND TYPECATIND ----------- ----------- 318568 2 318568 101087 318568 341151 318568 341152 4 record(s) selected. db2 => select * FROM PRODUCT.TYPECATEGORY WHERE TYPECATIND = 341126 TYPECATIND DESCRIPTION PARENTCATIND CATEGORYLEVEL ORDERNUM CHILDSORT OWNER VALID TAXONOMYCODE TAXONOMYDESCRIPTION ----------- -------------------------------------------------- ------------ --------------- -------- --------- ------------ ----- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 341126 New Project 341123 project 0 N SAGARJ Y 341126 New Project 341129 project 0 N QUEST Y 341126 New Project 341147 project 0 N QUEST Y 3 record(s) selected. db2 => select * FROM PRODUCT.CATEGORY WHERE PRODUCTIND = 318568 AND TYPECATIND in (select b.typecatind from product.category a, product.typecategory b where A.productInd = 318568 and a.typecatind = b.typecatind and b.categorylevel = 'project') PRODUCTIND TYPECATIND ----------- ----------- 0 record(s) selected. db2 => select * FROM PRODUCT.TYPECATEGORY WHERE TYPECATIND = 341126 TYPECATIND DESCRIPTION PARENTCATIND CATEGORYLEVEL ORDERNUM CHILDSORT OWNER VALID TAXONOMYCODE TAXONOMYDESCRIPTION ----------- -------------------------------------------------- ------------ --------------- -------- --------- ------------ ----- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 341126 New Project 341123 project 0 N SAGARJ Y 341126 New Project 341129 project 0 N QUEST Y 341126 New Project 341147 project 0 N QUEST Y 3 record(s) selected. db2 => select * FROM PRODUCT.CATEGORY WHERE PRODUCTIND = 318568 PRODUCTIND TYPECATIND ----------- ----------- 318568 2 318568 101087 318568 341151 318568 341152 4 record(s) selected. db2 => select * FROM PRODUCT.CATEGORY WHERE PRODUCTIND = 318568 PRODUCTIND TYPECATIND ----------- ----------- 318568 2 318568 101087 318568 341151 318568 341152 4 record(s) selected. db2 => INSERT INTO PRODUCT.CATEGORY (PRODUCTIND, TYPECATIND) values (318568, 341126 ) DB20000I The SQL command completed successfully. db2 => select * FROM PRODUCT.CATEGORY WHERE PRODUCTIND = 318568 PRODUCTIND TYPECATIND ----------- ----------- 318568 2 318568 101087 318568 341126 318568 341151 318568 341152 5 record(s) selected. db2 => select * FROM PRODUCT.CATEGORY WHERE PRODUCTIND = 318568 PRODUCTIND TYPECATIND ----------- ----------- 318568 2 318568 101087 318568 341126 318568 341151 318568 341152 5 record(s) selected. db2 => select * FROM PRODUCT.CATEGORY WHERE PRODUCTIND = 318570 PRODUCTIND TYPECATIND ----------- ----------- 318570 2 318570 101087 318570 341155 318570 341156 318570 341157 5 record(s) selected. db2 => select * FROM PRODUCT.CATEGORY WHERE PRODUCTIND = 318570 PRODUCTIND TYPECATIND ----------- ----------- 318570 2 318570 101087 318570 341155 318570 341157 318570 341158 5 record(s) selected. db2 => select a.typecatind from tigris.category a product.typecategory b where a.typecatind = b.typecatind and b.categorylevel='brand' and a.typecatind in(2) SQL0104N An unexpected token "product" was found following "om tigris.category a". Expected tokens may include: ",". SQLSTATE=42601 db2 => select a.typecatind from tigris.category a, product.typecategory b where a.typecatind = b.typecatind and b.categorylevel='brand' and a.typecatind in(2) TYPECATIND ----------- 0 record(s) selected. db2 => select a.typecatind from tigris.category a, product.typecategory b where a.typecatind = b.typecatind and b.categorylevel='brand' and a.typecatind in(101087) TYPECATIND ----------- 0 record(s) selected. db2 => select * from product.typecategory where categorylevel ='family' and parentcatind =2 TYPECATIND DESCRIPTION PARENTCATIND CATEGORYLEVEL ORDERNUM CHILDSORT OWNER VALID TAXONOMYCODE TAXONOMYDESCRIPTION ----------- -------------------------------------------------- ------------ --------------- -------- --------- ------------ ----- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 50003 Various 2 family 0 N DLENTZ Y HWZZZ Unclassified - HW Type 50053 Servers 2 family 0 N DLENTZ N HW183 xSeries Intel-Based Servers 50109 ThinkPad General 2 family 0 N DLENTZ Y HWZZZ Unclassified - HW Type 50207 Options 2 family 0 N VINCEL Y HW880 PC Options 50208 CDT 2 family 0 N VINCEL Y HWZZZ Unclassified - HW Type 50209 Server 2 family 0 N VINCEL N HWZZZ Unclassified - HW Type 50210 ThinkPad 2 family 0 N VINCEL Y HWZZZ Unclassified - HW Type 50211 Aptiva 2 family 0 N VINCEL Y HWZZZ Unclassified - HW Type 50226 Service Support Guide 2 family 0 N KBONEY Y HWZZZ Unclassified - HW Type 50262 Monitors 2 family 0 N AYANTE Y HW300 Monitors, Terminals 101087 NetVista 2 family 0 N MNOVAK Y HWD99 Other Desktop PCs 122438 ThinkCentre General 2 family 0 N MNOVAK Y HWZZZ Unclassified - HW Type 275162 Service Support Guide - STG 2 family 0 N DENNISS Y HWZZZ 13 record(s) selected. db2 => select a.typecatind from tigris.category a, product.typecategory b where a.typecatind = b.typecatind and b.categorylevel='brand' and a.typecatind in(50003) TYPECATIND ----------- 0 record(s) selected. db2 => select a.typecatind from tigris.category a, product.typecategory b where a.typecatind = b.typecatind and b.categorylevel='brand' and a.typecatind in(101087) TYPECATIND ----------- 0 record(s) selected. db2 => select a.typecatind,a.docind from tigris.category a, product.typecategory b where a.typecatind = b.typecatind and b.categorylevel='brand' and a.typecatind in(101087) TYPECATIND DOCIND ----------- ----------- 0 record(s) selected. db2 => select a.typecatind,a.docind,b.description,b.categorylevel from tigris.category a, product.typecategory b where a.typecatind = b.typecatind and b.categorylevel='brand' and a.typecatind in(101087) TYPECATIND DOCIND DESCRIPTION CATEGORYLEVEL ----------- ----------- -------------------------------------------------- --------------- 0 record(s) selected. db2 => select a.typecatind,a.docind,b.description,b.categorylevel from tigris.category a, product.typecategory b where a.typecatind = b.typecatind and b.categorylevel='brand' and a.typecatind in(275162) TYPECATIND DOCIND DESCRIPTION CATEGORYLEVEL ----------- ----------- -------------------------------------------------- --------------- 0 record(s) selected. db2 => select a.typecatind,a.docind,b.description,b.categorylevel from tigris.category a, product.typecategory b where a.typecatind = b.typecatind and b.categorylevel='brand' and a.typecatind in(350118) TYPECATIND DOCIND DESCRIPTION CATEGORYLEVEL ----------- ----------- -------------------------------------------------- --------------- 0 record(s) selected. db2 => select a.typecatind,a.docind,b.description,b.categorylevel from tigris.category a, product.typecategory b where a.typecatind = b.typecatind and b.categorylevel='family' and a.typecatind in(350118) TYPECATIND DOCIND DESCRIPTION CATEGORYLEVEL ----------- ----------- -------------------------------------------------- --------------- 0 record(s) selected. db2 => describe table product.typecategory Column Type Type name schema name Length Scale Nulls ------------------------------ --------- ------------------ -------- ----- ------ TYPECATIND SYSIBM INTEGER 4 0 No DESCRIPTION SYSIBM CHARACTER 50 0 No PARENTCATIND SYSIBM INTEGER 4 0 No CATEGORYLEVEL SYSIBM CHARACTER 15 0 No ORDERNUM SYSIBM SMALLINT 2 0 Yes CHILDSORT SYSIBM CHARACTER 1 0 No OWNER SYSIBM CHARACTER 12 0 Yes VALID SYSIBM CHARACTER 1 0 No TAXONOMYCODE SYSIBM VARCHAR 20 0 Yes TAXONOMYDESCRIPTION SYSIBM VARCHAR 256 0 Yes 10 record(s) selected. db2 => UPDATE PRODUCT.TYPECATEGORY SET TYPECATIND = 341158 DESCRIPTION = 'The See is beautiful' ORDERNUM = 0 CHILDSORT ='N' OWNER ='QUEST' VALID ='true' TAXONOMYCODE ='' TAXONOMYDESCRIPTION ='' WHERE TYPECATIND= 341160 AND PARENTCATIND =341161 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "DESCRIPTION" was found following "TYPECATIND = 341158". Expected tokens may include: "WHERE". SQLSTATE=42601 db2 => UPDATE PRODUCT.TYPECATEGORY SET TYPECATIND = 341158, DESCRIPTION = 'The See is beautiful' ORDERNUM = 0 CHILDSORT ='N' OWNER ='QUEST' VALID ='true' TAXONOMYCODE ='' TAXONOMYDESCRIPTION ='' WHERE TYPECATIND= 341160 AND PARENTCATIND =341161 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "ORDERNUM" was found following "he See is beautiful'". Expected tokens may include: "WHERE". SQLSTATE=42601 db2 =>