CREATE TABLE Failed. 3933: The Maximum Possible Row Length in the Table is too Large.

Database
Enthusiast

CREATE TABLE Failed. 3933: The Maximum Possible Row Length in the Table is too Large.

Hi All,

I am tying to create the following (and it did work) but was wondering if I can add the other datatypes I have commented out or make my varchar larger than 62000

REMRK_CLOB  VARCHAR(62000) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'REMARKS',

CREATE SET TABLE DTST_ADL_STG_METS.METS_ORDR_B ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT

     (

      JOB_ID INTEGER NOT NULL,

      STEP_ID INTEGER NOT NULL,

      MSG_ID INTEGER NOT NULL,

      MQ_MSG_ID VARBYTE(24) NOT NULL,

      MQ_PUT_TS_DTTM TIMESTAMP(6) TITLE 'MQ_PUT_TS' NOT NULL,

      ORDR_ID VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ORDERID' NOT NULL,

      ORDR_PRTY_DESC VARCHAR(64) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ORDERINGPARTY',

      BKNG_REF_NAME VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'BOOKINGREFERENCE',

      EMAIL_REF_DESC VARCHAR(64) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'EMAILREFERENCE',

      OLD_EMAIL_REF_DESC VARCHAR(64) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'OLDEMAILREFERENCE',

      VRSN_ID FLOAT TITLE 'VERSION',

      ORDR_TYPE_IND VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ORDERTYPE',

      ORDR_STATUS_IND VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ORDERSTATUS',

      ORDR_MODE_IND VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ORDERMODE',

      TRNS_TYPE_IND VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'TRANSPORTTYPE',

      PORT_CD VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'PORTCODE',

      BL_NUM VARCHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'BL',

      VSL_NAME VARCHAR(64) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'VESSELNAME',

      VSL_VOY_CD VARCHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'VESSELVOYAGE',

      VSL_CD VARCHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'VESSELCODE',

      ETA_NUM VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ETA',

      ETD_NUM VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'ETD',

      NEXT_PORT_NAME VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'NEXTPORT',

      PREV_PORT_NAME VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'PREVIOUSPORT',

      FINAL_DEST_NAME VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'FINALDESTINATION',

      LINE_CD VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'LINE',

      HAULG_REMRK_TXT VARCHAR(254) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'HAULAGEREMARKS',

      DIR_IND VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'DIRECTION',

      PRNCPL_IND VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'PRINCIPAL',

      CHNG_BY_NAME VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CHANGEDBY',

      CHNG_WHEN_DTTM TIMESTAMP(6) TITLE 'Column Date',

      VIP_CUST_ID VARCHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'VIPCUSTOMER',

      CUST_ID VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CUSTOMERID',

      VIA_PT_IND VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'VIAPOINTIND',

      FIN_SCOPE_IND VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'FINANCIALSCOPE',

      FAIL_ESRI_RESLV_CNT FLOAT TITLE 'FAILEDESRIRESOLVECOUNT',

      PICKUP_ORDR_IND VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'PICKUPORDERIND',

      EQUIP_POOL_ID VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'EQUIPMENTPOOLID',

      RTRN_DEPOT_NAME VARCHAR(32) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'RETURNDEPOT',

      REMRK_CLOB  VARCHAR(62000) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'REMARKS',

      --REMRK_CLOB1 Long Varchar CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'REMARKS',

      --REMRK_CLOB2 Long Nvarchar(64000) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'REMARKS',

      --REMRK_CLOB3 CLOB(64000) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'REMARKS',

      PRCSS_IND VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'PROCESSEDIND',

      FINAL_PORT_TXT VARCHAR(256) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'FINALPORT',

      POR_NAME VARCHAR(80) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'PLACEOFRECEIPT',

      POD_NAME VARCHAR(80) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'PLACEOFDELIVERY',

      MULTI_SHPMT_IND VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'MULTISHIPMENT',

      OPERTL_STAGE VARCHAR(13) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'OPERATIONALSTAGE',

      CARR_BOND_IND VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'CARRIERBOND',

      IN_BOND_NEED_IND VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'INBONDNEEDED',

      INT_DEL_REF_DESC VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'INTDELREFERENCE',

      MICH_IND VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'MICH',

      IMP_SHPMT_IND VARCHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'IMPORTSHIPMENT',

      TD_COMND_TYPE_IND VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'TD_COMMANDTYPE' NOT NULL,

      TD_UPDT_DATE_FOR_TD_DTTM TIMESTAMP(6) TITLE 'TD_UPDATEDDTFORTERRADATA' NOT NULL,

      MQ_MSG_ID_B BYTE(24))

PRIMARY INDEX ORDERID_PK ( ORDR_ID );

We need this to load large columns.

Best Regards

Richard

6 REPLIES
Enthusiast

Re: CREATE TABLE Failed. 3933: The Maximum Possible Row Length in the Table is too Large.

In Teradata, the maximum row size is approx 64K bytes. The table definition already contains one column of 62K bytes plus some other columns (which are not commented) and the size of the expected row still fits in 64K limit.

Adding any other column that makes the expected row size increase from 64K won't be allowed. One of the columns that you commented out contains another column with size 64K and so you end up with error.

Enthusiast

Re: CREATE TABLE Failed. 3933: The Maximum Possible Row Length in the Table is too Large.

Also depends upon the max Data Block size set by the DBA since a table row has to completely fit inside a Data Block.

Enthusiast

Re: CREATE TABLE Failed. 3933: The Maximum Possible Row Length in the Table is too Large.

Thank you both for the information. We will live with the 64K bytes. :)

Re: CREATE TABLE Failed. 3933: The Maximum Possible Row Length in the Table is too Large.

Do the text 'CHARACTER SET LATIN NOT CASESPECIFIC' always need to exist when creating a char type column?

What is the best approach to load data from a VSAM file to Teradata.

Senior Apprentice

Re: CREATE TABLE Failed. 3933: The Maximum Possible Row Length in the Table is too Large.

The CREATE was output of a SHOW TABLE, which automatically adds character set info.

If you don't specify it there's a default:

- for CHARACTER SET based on the default for the submitting user

- for CASESPECIFIC based on the session mode: CASESPECIFIC for ANSI and NOT CASESPECIFIC for Teradata sessions

Regarding VSAM, it's probably exporting the data and importing it using Teradata's load utilities like TPT.

Dieter

Re: CREATE TABLE Failed. 3933: The Maximum Possible Row Length in the Table is too Large.

Thanks for the info on 'CHARACTER SET LATIN NOT CASESPECIFIC'

Does Teradata Load Utilities capable of reading the column headings of the VSAM files (extracting to text file format with pipe delimited) and create the create table DDL script automatically or i have to manually create the table first.

I have a VSAM extract that has 104 columns and im trying to figure out how to generate the DDL scripts for Teradata as quick as possible.

We have MLOAD, TPUMP and Atana suite and it will be my first time to use these utilities.