?
Solved

SQLSTATE=54010 SQL0670N  DB2 9.5

Posted on 2009-04-21
8
Medium Priority
?
5,017 Views
Last Modified: 2012-05-06
trying to create a table from DDL generated script and I am getting a SQLSTATE=54010 SQL0670N error.  Code is attached.  Here is the error mesage:

CONNECT TO EDIJOB

   Database Connection Information

 Database server        = DB2/NT 9.5.0
 SQL authorization ID   = DBEAYON
 Local database alias   = EDIJOB


CREATE BUFFERPOOL "BUFPOOL32" SIZE AUTOMATIC PAGESIZE 32768
DB20000I  The SQL command completed successfully.

CONNECT RESET
DB20000I  The SQL command completed successfully.

CONNECT TO EDIJOB

   Database Connection Information

 Database server        = DB2/NT 9.5.0
 SQL authorization ID   = DBEAYON
 Local database alias   = EDIJOB


ALTER TABLESPACE SYSCATSPACE PREFETCHSIZE AUTOMATIC OVERHEAD 7.500000 FILE SYSTEM CACHING TRANSFERRATE 0.060000
DB20000I  The SQL command completed successfully.

ALTER TABLESPACE TEMPSPACE1 PREFETCHSIZE AUTOMATIC OVERHEAD 7.500000 FILE SYSTEM CACHING TRANSFERRATE 0.060000
DB20000I  The SQL command completed successfully.

ALTER TABLESPACE USERSPACE1 PREFETCHSIZE AUTOMATIC OVERHEAD 7.500000 FILE SYSTEM CACHING TRANSFERRATE 0.060000
DB20000I  The SQL command completed successfully.

CREATE TABLE "DB2ADMIN"."STRU_BCBSND"  ( "CASE_NUM" VARCHAR(12) , "COUNTY_CD" VARCHAR(2) , "CASE_TYPE" VARCHAR(12) , "N_P_FN" VARCHAR(12) , "N_P_LN" VARCHAR(17) , "N_P_MI" VARCHAR(1) , "N_P_SSN" VARCHAR(9) , "N_P_DOB" VARCHAR(8) , "N_2ND_FN" VARCHAR(12) , "N_2ND_LN" VARCHAR(17) , "N_2ND_MI" VARCHAR(1) , "N_2N_SSN" VARCHAR(9) , "N_2N_DOB" VARCHAR(8) , "N_2N_FN2" VARCHAR(12) , "N_2N_LN2" VARCHAR(17) , "N_2N_MI" VARCHAR(1) , "N_2N_SSN2" VARCHAR(9) , "N_2N_DOB2" VARCHAR(8) , "N_H_ADD1" VARCHAR(30) , "N_H_ADD2" VARCHAR(30) , "N_H_ADD3" VARCHAR(30) , "N_H_ADD4" VARCHAR(30) , "N_H_CITY" VARCHAR(20) , "N_STATE" VARCHAR(2) , "N_ZIP" VARCHAR(9) , "N_MAIL_AD1" VARCHAR(30) , "N_MAIL_AD2" VARCHAR(30) , "N_MAIL_AD3" VARCHAR(30) , "N_MAIL_AD4" VARCHAR(30) , "N_MAIL_CTY" VARCHAR(20) , "N_MAIL_ST" VARCHAR(2) , "N_MAIL_ZIP" VARCHAR(9) , "CP_P_FN" VARCHAR(12) , "CP_P_LN" VARCHAR(17) , "CP_P_MI" VARCHAR(1) , "CP_P_SSN" VARCHAR(9) , "CP_P_DOB" VARCHAR(8) , "CP_2ND_FN" VARCHAR(12) , "CP_2ND_LN" VARCHAR(17) , "CP_2ND_MI" VARCHAR(1) , "CP_2ND_SSN" VARCHAR(9) , "CP_2ND_DOB" VARCHAR(8) , "CP_2N_FN2" VARCHAR(12) , "CP_2N_LN2" VARCHAR(17) , "CP_2N_MI" VARCHAR(1) , "CP_2N_SSN" VARCHAR(9) , "CP_2N_DOB2" VARCHAR(8) , "CP_H_ADD1" VARCHAR(30) , "CP_H_ADD2" VARCHAR(30) , "CP_H_ADD3" VARCHAR(30) , "CP_H_ADD4" VARCHAR(30) , "CP_H_CITY" VARCHAR(20) , "CP_H_ST" VARCHAR(2) , "CP_H_ZIP" VARCHAR(9) , "CP_M_ADD1" VARCHAR(30) , "CP_M_ADD2" VARCHAR(30) , "CP_M_ADD3" VARCHAR(30) , "CP_M_ADD4" VARCHAR(30) , "CP_M_CITY" VARCHAR(20) , "CP_M_STATE" VARCHAR(2) , "CP_M_ZIP" VARCHAR(9) , "CH_P_FN" VARCHAR(12) , "CH_P_LN" VARCHAR(17) , "CH_P_MI" VARCHAR(1) , "CH_P_SSN" VARCHAR(9) , "CH_P_DOB" VARCHAR(8) , "CH_2N_FN" VARCHAR(12) , "CH_2N_LN" VARCHAR(17) , "CH_2N_MI" VARCHAR(1) , "CH_2N_SSN" VARCHAR(9) , "CH_2N_DOB" VARCHAR(8) , "CH_2N_FN2" VARCHAR(12) , "CH_2N_LN2" VARCHAR(17) , "CH_2N_MI2" VARCHAR(1) , "CH_2N_SSN2" VARCHAR(9) , "CH_2N_DOB2" VARCHAR(8) , "CH_H_ADD1" VARCHAR(30) , "CH_H_ADD2" VARCHAR(30) , "CH_H_ADD3" VARCHAR(30) , "CH_H_ADD4" VARCHAR(30) , "CH_H_CITY" VARCHAR(20) , "CH_H_STATE" VARCHAR(2) , "CH_H_ZIP" VARCHAR(9) , "CH_M_ADD1" VARCHAR(30) , "CH_M_ADD2" VARCHAR(30) , "CH_M_ADD3" VARCHAR(30) , "CH_M_ADD4" VARCHAR(30) , "CH_M_CITY" VARCHAR(20) , "CH_M_STATE" VARCHAR(2) , "CH_M_ZIP" VARCHAR(9) , "WAGE_IND1" VARCHAR(14) , "EMP_EIN1" VARCHAR(9) , "EMP_NAME1" VARCHAR(40) , "EMP_ADD1_1" VARCHAR(30) , "EMP_ADD1_2" VARCHAR(30) , "EMP_ADD1_3" VARCHAR(30) , "EMP_ADD1_4" VARCHAR(30) , "EMP_CITY1" VARCHAR(20) , "EMP_ST1" VARCHAR(2) , "EMP_ZIP1" VARCHAR(9) , "EMP_EMAIL1" VARCHAR(20) , "EMP_ST_DT1" VARCHAR(8) , "EMP_ENDDT1" VARCHAR(8) , "WAGEQTR1_1" VARCHAR(5) , "WAGEAMT1_1" VARCHAR(9) , "WAGEQTR1_2" VARCHAR(5) , "WAGEAMT1_2" VARCHAR(9) , "WAGEQTR1_3" VARCHAR(5) , "WAGEAMT1_3" VARCHAR(9) , "WAGEQTR1_4" VARCHAR(5) , "WAGEAMT1_4" VARCHAR(9) , "WAGE_IND2" VARCHAR(14) , "EMPLR_EIN2" VARCHAR(9) , "EMPLR_NM2" VARCHAR(40) , "EMP_ADR2_1" VARCHAR(30) , "EMP_ADR2_2" VARCHAR(30) , "EMP_ADR2_3" VARCHAR(30) , "EMP_ADR2_4" VARCHAR(30) , "EMP_CITY_2" VARCHAR(20) , "EMP_STATE2" VARCHAR(2) , "EMP_ZIP_2" VARCHAR(9) , "EMP_EMAIL2" VARCHAR(20) , "EMP_STDTE2" VARCHAR(8) , "EMP_ENDDT2" VARCHAR(8) , "WGE_QTR1_2" VARCHAR(5) , "WGE_AMT1_2" VARCHAR(9) , "WGE_QTR2_2" VARCHAR(5) , "WGE_AMT2_2" VARCHAR(9) , "WGE_QTR3_2" VARCHAR(5) , "WGE_AMT3_2" VARCHAR(9) , "WGE_QTR4_2" VARCHAR(5) , "WGE_AMT4_2" VARCHAR(9) , "WAGE_IND_3" VARCHAR(14) , "EMP_EIN_3" VARCHAR(9) , "EMP_NAME_3" VARCHAR(40) , "EMP_ADR1_3" VARCHAR(30) , "EMP_AD2_3" VARCHAR(30) , "EMP_AD3_3" VARCHAR(30) , "EMP_AD4_3" VARCHAR(30) , "EMP_CITY_3" VARCHAR(20) , "EMP_STATE3" VARCHAR(2) , "EMP_ZIP_3" VARCHAR(9) , "EMP_EMAIL3" VARCHAR(20) , "EMP_STDTE3" VARCHAR(8) , "EMP_ENDDT3" VARCHAR(8) , "WGE_QTR1_3" VARCHAR(5) , "WGE_AMT1_3" VARCHAR(9) , "WGE_QTR2_3" VARCHAR(5) , "WGE_AMT2_3" VARCHAR(9) , "WGE_QTR3_3" VARCHAR(5) , "WGE_AMT3_3" VARCHAR(9) , "WGE_QTR4_3" VARCHAR(5) , "WGE_AMT4_3" VARCHAR(9) , "CH_SUPORDI" VARCHAR(1) , "CHSP_LSTPM" VARCHAR(8) , "CHSP_LSTPD" VARCHAR(8) , "NMSN_SENT" VARCHAR(1) , "MDSP_ORDIN" VARCHAR(1) , "DTE_SP_ORD" VARCHAR(8) , "DOMESTIC_V" VARCHAR(1) , "INS_CAR_CT" VARCHAR(3) , "INS_CARNM1" VARCHAR(50) , "INSPLCYHD1" VARCHAR(40) , "INSPLCYID1" VARCHAR(25) , "INSGRPNUM1" VARCHAR(12) , "INSCOVTYP1" VARCHAR(1) , "INSEFFDTE1" VARCHAR(8) , "INSCANDTE1" VARCHAR(8) , "INS_CARNM2" VARCHAR(50) , "INSPLCYHD2" VARCHAR(40) , "INSPLCYID2" VARCHAR(25) , "INSGRPNUM2" VARCHAR(12) , "INSCOVTYP2" VARCHAR(1) , "INSEFFDTE2" VARCHAR(8) , "INSCANDTE2" VARCHAR(8) , "INS_CARNM3" VARCHAR(50) , "INSPLCYHD3" VARCHAR(40) , "INSPLCYID3" VARCHAR(25) , "INSGRPNUM3" VARCHAR(12) , "INSCOVTYP3" VARCHAR(1) , "INSEFFDTE3" VARCHAR(8) , "INSCANDTE3" VARCHAR(8) , "INS_CARNM4" VARCHAR(50) , "INSPLCYHD4" VARCHAR(40) , "INSPLCYID4" VARCHAR(25) , "INSGRPNUM4" VARCHAR(12) , "INSCOVTYP4" VARCHAR(1) , "INSEFFDTE4" VARCHAR(8) , "INSCANDTE4" VARCHAR(8) , "INS_CARNM5" VARCHAR(50) , "INSPLCYHD5" VARCHAR(40) , "INSPLCYID5" VARCHAR(25) , "INSGRPNUM5" VARCHAR(12) , "INSCOVTYP5" VARCHAR(1) , "INSEFFDTE5" VARCHAR(8) , "INSCANDTE5" VARCHAR(8) , "INS_CARNM6" VARCHAR(50) , "INSPLCYHD6" VARCHAR(40) , "INSPLCYID6" VARCHAR(25) , "INSGRPNUM6" VARCHAR(12) , "INSCOVTYP6" VARCHAR(1) , "INSEFFDTE6" VARCHAR(8) , "INSCANDTE6" VARCHAR(8) , "INS_CARNM7" VARCHAR(50) , "INSPLCYHD7" VARCHAR(40) , "INSPLCYID7" VARCHAR(25) , "INSGRPNUM7" VARCHAR(12) , "INSCOVTYP7" VARCHAR(1) , "INSEFFDTE7" VARCHAR(8) , "INSCANDTE7" VARCHAR(8) , "INS_CARNM8" VARCHAR(50) , "INSPLCYHD8" VARCHAR(40) , "INSPLCYID8" VARCHAR(25) , "INSGRPNUM8" VARCHAR(12) , "INSCOVTYP8" VARCHAR(1) , "INSEFFDTE8" VARCHAR(8) , "INSCANDTE8" VARCHAR(8) , "INS_CARNM9" VARCHAR(50) , "INSPLCYHD9" VARCHAR(40) , "INSPLCYID9" VARCHAR(25) , "INSGRPNUM9" VARCHAR(12) , "INSCOVTYP9" VARCHAR(1) , "INSEFFDTE9" VARCHAR(8) , "INSCANDTE9" VARCHAR(8) , "INS_CARNM0" VARCHAR(50) , "INSPLCYHD0" VARCHAR(40) , "INSPLCYID0" VARCHAR(25) , "INSGRPNUM0" VARCHAR(12) , "INSCOVTYP0" VARCHAR(1) , "INSEFFDTE0" VARCHAR(8) , "INSCANDTE0" VARCHAR(8) , "REC_NBR" VARCHAR(9) , "DB2RECID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +1 INCREMENT BY +1 MINVALUE +1 MAXVALUE +2147483647 NO CYCLE NO CACHE NO ORDER ) ) IN "USERSPACE1"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0670N  The row length of the table exceeded a limit of "4005" bytes.  
(Table space "USERSPACE1".)  SQLSTATE=54010

ALTER TABLE "DB2ADMIN"."STRU_BCBSND" ADD CONSTRAINT "PK_BCBSND" PRIMARY KEY ("DB2RECID")
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "DB2ADMIN.STRU_BCBSND" is an undefined name.  SQLSTATE=42704

COMMIT WORK
DB20000I  The SQL command completed successfully.

CONNECT RESET
DB20000I  The SQL command completed successfully.

TERMINATE
DB20000I  The TERMINATE command completed successfully.


-- This CLP file was created using DB2LOOK Version 9.1
-- Timestamp: 4/21/2009 12:26:52 PM
-- Database Name: EDIJOB         
-- Database Manager Version: DB2/NT Version 9.1.3          
-- Database Codepage: 1252
-- Database Collating Sequence is: UNIQUE
 
 
CONNECT TO EDIJOB;
 
------------------------------------
-- DDL Statements for BUFFERPOOLS --
------------------------------------
CREATE BUFFERPOOL "BUFPOOL32" SIZE AUTOMATIC PAGESIZE 32768 NUMBLOCKPAGES 122 BLOCKSIZE 32 NOT EXTENDED STORAGE;
 
CONNECT RESET;
CONNECT TO EDIJOB;
 
-- Mimic tablespace
 
ALTER TABLESPACE SYSCATSPACE
      PREFETCHSIZE AUTOMATIC
      OVERHEAD 7.500000
      FILE SYSTEM CACHING 
      TRANSFERRATE 0.060000;
 
 
ALTER TABLESPACE TEMPSPACE1
      PREFETCHSIZE AUTOMATIC
      OVERHEAD 7.500000
      FILE SYSTEM CACHING 
      TRANSFERRATE 0.060000;
 
 
ALTER TABLESPACE USERSPACE1
      PREFETCHSIZE AUTOMATIC
      OVERHEAD 7.500000
      FILE SYSTEM CACHING 
      TRANSFERRATE 0.060000;
 
 
------------------------------------------------
-- DDL Statements for table "DB2ADMIN"."STRU_BCBSND"
------------------------------------------------
 
 
CREATE TABLE "DB2ADMIN"."STRU_BCBSND"  (
		  "CASE_NUM" VARCHAR(12) , 
		  "COUNTY_CD" VARCHAR(2) , 
		  "CASE_TYPE" VARCHAR(12) , 
		  "N_P_FN" VARCHAR(12) , 
		  "N_P_LN" VARCHAR(17) , 
		  "N_P_MI" VARCHAR(1) , 
		  "N_P_SSN" VARCHAR(9) , 
		  "N_P_DOB" VARCHAR(8) , 
		  "N_2ND_FN" VARCHAR(12) , 
		  "N_2ND_LN" VARCHAR(17) , 
		  "N_2ND_MI" VARCHAR(1) , 
		  "N_2N_SSN" VARCHAR(9) , 
		  "N_2N_DOB" VARCHAR(8) , 
		  "N_2N_FN2" VARCHAR(12) , 
		  "N_2N_LN2" VARCHAR(17) , 
		  "N_2N_MI" VARCHAR(1) , 
		  "N_2N_SSN2" VARCHAR(9) , 
		  "N_2N_DOB2" VARCHAR(8) , 
		  "N_H_ADD1" VARCHAR(30) , 
		  "N_H_ADD2" VARCHAR(30) , 
		  "N_H_ADD3" VARCHAR(30) , 
		  "N_H_ADD4" VARCHAR(30) , 
		  "N_H_CITY" VARCHAR(20) , 
		  "N_STATE" VARCHAR(2) , 
		  "N_ZIP" VARCHAR(9) , 
		  "N_MAIL_AD1" VARCHAR(30) , 
		  "N_MAIL_AD2" VARCHAR(30) , 
		  "N_MAIL_AD3" VARCHAR(30) , 
		  "N_MAIL_AD4" VARCHAR(30) , 
		  "N_MAIL_CTY" VARCHAR(20) , 
		  "N_MAIL_ST" VARCHAR(2) , 
		  "N_MAIL_ZIP" VARCHAR(9) , 
		  "CP_P_FN" VARCHAR(12) , 
		  "CP_P_LN" VARCHAR(17) , 
		  "CP_P_MI" VARCHAR(1) , 
		  "CP_P_SSN" VARCHAR(9) , 
		  "CP_P_DOB" VARCHAR(8) , 
		  "CP_2ND_FN" VARCHAR(12) , 
		  "CP_2ND_LN" VARCHAR(17) , 
		  "CP_2ND_MI" VARCHAR(1) , 
		  "CP_2ND_SSN" VARCHAR(9) , 
		  "CP_2ND_DOB" VARCHAR(8) , 
		  "CP_2N_FN2" VARCHAR(12) , 
		  "CP_2N_LN2" VARCHAR(17) , 
		  "CP_2N_MI" VARCHAR(1) , 
		  "CP_2N_SSN" VARCHAR(9) , 
		  "CP_2N_DOB2" VARCHAR(8) , 
		  "CP_H_ADD1" VARCHAR(30) , 
		  "CP_H_ADD2" VARCHAR(30) , 
		  "CP_H_ADD3" VARCHAR(30) , 
		  "CP_H_ADD4" VARCHAR(30) , 
		  "CP_H_CITY" VARCHAR(20) , 
		  "CP_H_ST" VARCHAR(2) , 
		  "CP_H_ZIP" VARCHAR(9) , 
		  "CP_M_ADD1" VARCHAR(30) , 
		  "CP_M_ADD2" VARCHAR(30) , 
		  "CP_M_ADD3" VARCHAR(30) , 
		  "CP_M_ADD4" VARCHAR(30) , 
		  "CP_M_CITY" VARCHAR(20) , 
		  "CP_M_STATE" VARCHAR(2) , 
		  "CP_M_ZIP" VARCHAR(9) , 
		  "CH_P_FN" VARCHAR(12) , 
		  "CH_P_LN" VARCHAR(17) , 
		  "CH_P_MI" VARCHAR(1) , 
		  "CH_P_SSN" VARCHAR(9) , 
		  "CH_P_DOB" VARCHAR(8) , 
		  "CH_2N_FN" VARCHAR(12) , 
		  "CH_2N_LN" VARCHAR(17) , 
		  "CH_2N_MI" VARCHAR(1) , 
		  "CH_2N_SSN" VARCHAR(9) , 
		  "CH_2N_DOB" VARCHAR(8) , 
		  "CH_2N_FN2" VARCHAR(12) , 
		  "CH_2N_LN2" VARCHAR(17) , 
		  "CH_2N_MI2" VARCHAR(1) , 
		  "CH_2N_SSN2" VARCHAR(9) , 
		  "CH_2N_DOB2" VARCHAR(8) , 
		  "CH_H_ADD1" VARCHAR(30) , 
		  "CH_H_ADD2" VARCHAR(30) , 
		  "CH_H_ADD3" VARCHAR(30) , 
		  "CH_H_ADD4" VARCHAR(30) , 
		  "CH_H_CITY" VARCHAR(20) , 
		  "CH_H_STATE" VARCHAR(2) , 
		  "CH_H_ZIP" VARCHAR(9) , 
		  "CH_M_ADD1" VARCHAR(30) , 
		  "CH_M_ADD2" VARCHAR(30) , 
		  "CH_M_ADD3" VARCHAR(30) , 
		  "CH_M_ADD4" VARCHAR(30) , 
		  "CH_M_CITY" VARCHAR(20) , 
		  "CH_M_STATE" VARCHAR(2) , 
		  "CH_M_ZIP" VARCHAR(9) , 
		  "WAGE_IND1" VARCHAR(14) , 
		  "EMP_EIN1" VARCHAR(9) , 
		  "EMP_NAME1" VARCHAR(40) , 
		  "EMP_ADD1_1" VARCHAR(30) , 
		  "EMP_ADD1_2" VARCHAR(30) , 
		  "EMP_ADD1_3" VARCHAR(30) , 
		  "EMP_ADD1_4" VARCHAR(30) , 
		  "EMP_CITY1" VARCHAR(20) , 
		  "EMP_ST1" VARCHAR(2) , 
		  "EMP_ZIP1" VARCHAR(9) , 
		  "EMP_EMAIL1" VARCHAR(20) , 
		  "EMP_ST_DT1" VARCHAR(8) , 
		  "EMP_ENDDT1" VARCHAR(8) , 
		  "WAGEQTR1_1" VARCHAR(5) , 
		  "WAGEAMT1_1" VARCHAR(9) , 
		  "WAGEQTR1_2" VARCHAR(5) , 
		  "WAGEAMT1_2" VARCHAR(9) , 
		  "WAGEQTR1_3" VARCHAR(5) , 
		  "WAGEAMT1_3" VARCHAR(9) , 
		  "WAGEQTR1_4" VARCHAR(5) , 
		  "WAGEAMT1_4" VARCHAR(9) , 
		  "WAGE_IND2" VARCHAR(14) , 
		  "EMPLR_EIN2" VARCHAR(9) , 
		  "EMPLR_NM2" VARCHAR(40) , 
		  "EMP_ADR2_1" VARCHAR(30) , 
		  "EMP_ADR2_2" VARCHAR(30) , 
		  "EMP_ADR2_3" VARCHAR(30) , 
		  "EMP_ADR2_4" VARCHAR(30) , 
		  "EMP_CITY_2" VARCHAR(20) , 
		  "EMP_STATE2" VARCHAR(2) , 
		  "EMP_ZIP_2" VARCHAR(9) , 
		  "EMP_EMAIL2" VARCHAR(20) , 
		  "EMP_STDTE2" VARCHAR(8) , 
		  "EMP_ENDDT2" VARCHAR(8) , 
		  "WGE_QTR1_2" VARCHAR(5) , 
		  "WGE_AMT1_2" VARCHAR(9) , 
		  "WGE_QTR2_2" VARCHAR(5) , 
		  "WGE_AMT2_2" VARCHAR(9) , 
		  "WGE_QTR3_2" VARCHAR(5) , 
		  "WGE_AMT3_2" VARCHAR(9) , 
		  "WGE_QTR4_2" VARCHAR(5) , 
		  "WGE_AMT4_2" VARCHAR(9) , 
		  "WAGE_IND_3" VARCHAR(14) , 
		  "EMP_EIN_3" VARCHAR(9) , 
		  "EMP_NAME_3" VARCHAR(40) , 
		  "EMP_ADR1_3" VARCHAR(30) , 
		  "EMP_AD2_3" VARCHAR(30) , 
		  "EMP_AD3_3" VARCHAR(30) , 
		  "EMP_AD4_3" VARCHAR(30) , 
		  "EMP_CITY_3" VARCHAR(20) , 
		  "EMP_STATE3" VARCHAR(2) , 
		  "EMP_ZIP_3" VARCHAR(9) , 
		  "EMP_EMAIL3" VARCHAR(20) , 
		  "EMP_STDTE3" VARCHAR(8) , 
		  "EMP_ENDDT3" VARCHAR(8) , 
		  "WGE_QTR1_3" VARCHAR(5) , 
		  "WGE_AMT1_3" VARCHAR(9) , 
		  "WGE_QTR2_3" VARCHAR(5) , 
		  "WGE_AMT2_3" VARCHAR(9) , 
		  "WGE_QTR3_3" VARCHAR(5) , 
		  "WGE_AMT3_3" VARCHAR(9) , 
		  "WGE_QTR4_3" VARCHAR(5) , 
		  "WGE_AMT4_3" VARCHAR(9) , 
		  "CH_SUPORDI" VARCHAR(1) , 
		  "CHSP_LSTPM" VARCHAR(8) , 
		  "CHSP_LSTPD" VARCHAR(8) , 
		  "NMSN_SENT" VARCHAR(1) , 
		  "MDSP_ORDIN" VARCHAR(1) , 
		  "DTE_SP_ORD" VARCHAR(8) , 
		  "DOMESTIC_V" VARCHAR(1) , 
		  "INS_CAR_CT" VARCHAR(3) , 
		  "INS_CARNM1" VARCHAR(50) , 
		  "INSPLCYHD1" VARCHAR(40) , 
		  "INSPLCYID1" VARCHAR(25) , 
		  "INSGRPNUM1" VARCHAR(12) , 
		  "INSCOVTYP1" VARCHAR(1) , 
		  "INSEFFDTE1" VARCHAR(8) , 
		  "INSCANDTE1" VARCHAR(8) , 
		  "INS_CARNM2" VARCHAR(50) , 
		  "INSPLCYHD2" VARCHAR(40) , 
		  "INSPLCYID2" VARCHAR(25) , 
		  "INSGRPNUM2" VARCHAR(12) , 
		  "INSCOVTYP2" VARCHAR(1) , 
		  "INSEFFDTE2" VARCHAR(8) , 
		  "INSCANDTE2" VARCHAR(8) , 
		  "INS_CARNM3" VARCHAR(50) , 
		  "INSPLCYHD3" VARCHAR(40) , 
		  "INSPLCYID3" VARCHAR(25) , 
		  "INSGRPNUM3" VARCHAR(12) , 
		  "INSCOVTYP3" VARCHAR(1) , 
		  "INSEFFDTE3" VARCHAR(8) , 
		  "INSCANDTE3" VARCHAR(8) , 
		  "INS_CARNM4" VARCHAR(50) , 
		  "INSPLCYHD4" VARCHAR(40) , 
		  "INSPLCYID4" VARCHAR(25) , 
		  "INSGRPNUM4" VARCHAR(12) , 
		  "INSCOVTYP4" VARCHAR(1) , 
		  "INSEFFDTE4" VARCHAR(8) , 
		  "INSCANDTE4" VARCHAR(8) , 
		  "INS_CARNM5" VARCHAR(50) , 
		  "INSPLCYHD5" VARCHAR(40) , 
		  "INSPLCYID5" VARCHAR(25) , 
		  "INSGRPNUM5" VARCHAR(12) , 
		  "INSCOVTYP5" VARCHAR(1) , 
		  "INSEFFDTE5" VARCHAR(8) , 
		  "INSCANDTE5" VARCHAR(8) , 
		  "INS_CARNM6" VARCHAR(50) , 
		  "INSPLCYHD6" VARCHAR(40) , 
		  "INSPLCYID6" VARCHAR(25) , 
		  "INSGRPNUM6" VARCHAR(12) , 
		  "INSCOVTYP6" VARCHAR(1) , 
		  "INSEFFDTE6" VARCHAR(8) , 
		  "INSCANDTE6" VARCHAR(8) , 
		  "INS_CARNM7" VARCHAR(50) , 
		  "INSPLCYHD7" VARCHAR(40) , 
		  "INSPLCYID7" VARCHAR(25) , 
		  "INSGRPNUM7" VARCHAR(12) , 
		  "INSCOVTYP7" VARCHAR(1) , 
		  "INSEFFDTE7" VARCHAR(8) , 
		  "INSCANDTE7" VARCHAR(8) , 
		  "INS_CARNM8" VARCHAR(50) , 
		  "INSPLCYHD8" VARCHAR(40) , 
		  "INSPLCYID8" VARCHAR(25) , 
		  "INSGRPNUM8" VARCHAR(12) , 
		  "INSCOVTYP8" VARCHAR(1) , 
		  "INSEFFDTE8" VARCHAR(8) , 
		  "INSCANDTE8" VARCHAR(8) , 
		  "INS_CARNM9" VARCHAR(50) , 
		  "INSPLCYHD9" VARCHAR(40) , 
		  "INSPLCYID9" VARCHAR(25) , 
		  "INSGRPNUM9" VARCHAR(12) , 
		  "INSCOVTYP9" VARCHAR(1) , 
		  "INSEFFDTE9" VARCHAR(8) , 
		  "INSCANDTE9" VARCHAR(8) , 
		  "INS_CARNM0" VARCHAR(50) , 
		  "INSPLCYHD0" VARCHAR(40) , 
		  "INSPLCYID0" VARCHAR(25) , 
		  "INSGRPNUM0" VARCHAR(12) , 
		  "INSCOVTYP0" VARCHAR(1) , 
		  "INSEFFDTE0" VARCHAR(8) , 
		  "INSCANDTE0" VARCHAR(8) , 
		  "REC_NBR" VARCHAR(9) , 
		  "DB2RECID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (  
		    START WITH +1  
		    INCREMENT BY +1  
		    MINVALUE +1  
		    MAXVALUE +2147483647  
		    NO CYCLE  
		    NO CACHE  
		    NO ORDER ) )   
		 IN "USERSPACE1" ; 
 
 
-- DDL Statements for primary key on Table "DB2ADMIN"."STRU_BCBSND"
 
ALTER TABLE "DB2ADMIN"."STRU_BCBSND" 
	ADD CONSTRAINT "PK_BCBSND" PRIMARY KEY
		("DB2RECID");
 
 
 
 
 
 
 
 
 
COMMIT WORK;
 
CONNECT RESET;
 
TERMINATE;
 
-- Generate statistics for all creators 
-- The db2look utility will consider only the specified tables 
-- Creating DDL for table(s)
;

Open in new window

0
Comment
Question by:dbeayon
  • 4
  • 3
8 Comments
 
LVL 18

Expert Comment

by:Dave Ford
ID: 24199980

According to the error message, your row is too long:

"The row length of the table exceeded a limit of 4005 bytes"

Just as a test, try a drastically smaller row length.

-- DaveSlash
0
 

Author Comment

by:dbeayon
ID: 24200188
reducing the row length does allow the command to work.  however this does not meet my needs.  since this ddl was generated from an existing db2 table, then there has to be a wat to get this to work.  i am asking for a way to accept these rows.  the 4005 bytes is for a 4k pagesize.  
I should be asking for a 32k pagesize here:
CREATE BUFFERPOOL "BUFPOOL32" SIZE AUTOMATIC PAGESIZE 32768 NUMBLOCKPAGES 122 BLOCKSIZE 32 NOT EXTENDED STORAGE;
 
 
 
 
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24201596
well,
you must use a bigger page size since you can't fit that row in a 4kb page, so i guess you answered your own question
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 24201607
if you do want to fit it to a 4kb page you can try to make the record shorter, for example by converting varchar(1) columns to char(1) columns,
this will save 2 bytes for each converted column, but the application needs to know how to support it (since a blank string will now be ' ' and not '')
0
 

Author Comment

by:dbeayon
ID: 24202011
the original DDL statement that I provided creates the 32k pagesize.  so that is not the answer
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 24202071
yes,
but when the tablespace userspace1 was created (i assume by default when you created the database) it was assigned to a default bufferpool with pagesize of 4kb
0
 

Author Comment

by:dbeayon
ID: 24209331
I created a new database,this time putting the default bufferpool and pagesize to 32k and the DDL command ran fine.
Is there a way to change the default pagesize to 32k for the Database that has 4k limits?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24212189
you can't
you have to specify the default tablespace when you create the database
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question