dbeayon
asked on
SQLSTATE=54010 SQL0670N DB2 9.5
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.
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)
;
ASKER
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;
I should be asking for a 32k pagesize here:
CREATE BUFFERPOOL "BUFPOOL32" SIZE AUTOMATIC PAGESIZE 32768 NUMBLOCKPAGES 122 BLOCKSIZE 32 NOT EXTENDED STORAGE;
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
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
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 '')
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 '')
ASKER
the original DDL statement that I provided creates the 32k pagesize. so that is not the answer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
Is there a way to change the default pagesize to 32k for the Database that has 4k limits?
you can't
you have to specify the default tablespace when you create the database
you have to specify the default tablespace when you create the database
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