Posted: Wed Sep 10, 2003 4:37 am Post subject: SQLCODE -408
Hi,
I am taking the input from Flat Files and inserting into the Tables.
Compilation is fine and while binding it gives -408 SQLCODE for one
column which is declared as Decimal(11,0) in the table.
Even,if I try to go-away-spammer-sucker the value, it is giving the same error in
Binding.
But same if I try to insert through QMF, I am able to insert into the
table.
The Explanation of SQLCODE -408 is as follow:
******************
-408 THE VALUE IS NOT COMPATIBLE WITH THE DATA TYPE OF ITS TARGET
Explanation: The data type of the value to be assigned to the column,
parameter, host variable or transition variable by the SQL statement is
incompatible with the declared data type of the assignment target. Both
must be:
o Numeric
o Character
o Graphic
o Dates or character
o Times or character
o Timestamps or character
o Row IDs
o The same distinct types
******************
I need to upload all the datas from the Files to tables as soon as
possible based on some business rule.
Please let me know all the possible causes and the rectification of it.
Thanks in advance. _________________ Have a Great Day.
Thanks & Regards,
Jai
There are 78 Columns and I checked it. Please let me know if you find the solution. The bind error is coming in Act_subor_se_ct which is declared as Decimal(11,0)
Please find the Table Declaration:
EXEC SQL DECLARE SE_DETAIL TABLE
( ACT_SUBOR_CAP_CT DECIMAL(11, 0) NOT NULL,
CUR_PLAN_TX CHAR(30) NOT NULL,
DUAL_AFFL_CAP_NO CHAR(10) NOT NULL,
PLAN_UPDT_TX CHAR(30) NOT NULL,
SE_SUBM_CAP_IN CHAR(1) NOT NULL,
AEBA_MER_IN VARCHAR(1) NOT NULL,
CAP_OWN_IN CHAR(1) NOT NULL,
CAP_FRNCHS_IN CHAR(1) NOT NULL,
SKI_RT_LVL_CD CHAR(1) NOT NULL,
TOC_MIXED_INDUS_IN CHAR(1) NOT NULL,
TOC_IN CHAR(1) NOT NULL,
LDG_RT_LVL_CD CHAR(1) NOT NULL,
RPT_CHAN_CD CHAR(5) NOT NULL,
REV_XFER_IN CHAR(1) NOT NULL,
ORIG_REV_ID CHAR(5) NOT NULL,
LANG_PREF_CD CHAR(2) NOT NULL,
ARB_LET_CD CHAR(4) NOT NULL,
SE_NO CHAR(10) NOT NULL,
EDC_RT_UPDT_DT DATE NOT NULL,
CPC_RT_UPDT_DT DATE NOT NULL,
SE_HIER_CD CHAR(1) NOT NULL,
PLAN_UPDT_DT DATE NOT NULL,
ROC_VOL_UPDT_DT DATE NOT NULL,
SEIMS_STA_CD CHAR(1) NOT NULL,
ACCT_BYPASS_CD CHAR(3) NOT NULL,
ACCT_BYPASS_RSN_CD CHAR(2) NOT NULL,
SPEC_SERV_PROG_CD CHAR(3) NOT NULL,
SEIMS_MER_EDGE_CD CHAR(3) NOT NULL,
PAY_FREQ_CD CHAR(2) NOT NULL,
MJR_ACCT_CLASS_CD CHAR(2) NOT NULL,
SUB_ACCT_CLASS_CD CHAR(2) NOT NULL,
DTL_ACCT_CLASS_CD CHAR(2) NOT NULL,
ACT_SUBOR_SE_CT DECIMAL(11, 0) NOT NULL,
TERR_MAN_ID CHAR(5) NOT NULL,
PREV_TERR_MAN_ID CHAR(5) NOT NULL,
CAP_BORDER_IN CHAR(1) NOT NULL,
CTRY_CD CHAR(2) NOT NULL,
SUB_CTRY_CD CHAR(2) NOT NULL,
CURR_CD CHAR(3) NOT NULL,
BEST_RT_CD CHAR(1) NOT NULL,
PAY_OPTN_CD CHAR(2) NOT NULL,
SE_DUAL_AFFL_IN CHAR(1) NOT NULL,
CANC_DT DATE NOT NULL,
CHAIN_OPEN_DT DATE NOT NULL,
SE_PURE_CPC_IN CHAR(1) NOT NULL,
SE_PIG_MER_IN CHAR(1) NOT NULL,
SKI_EDC_RT DECIMAL(3, 2) NOT NULL,
SIMTX_PARM_RUN_DT DATE NOT NULL,
PROPOSED_PLAN_TX CHAR(30) NOT NULL,
SIMTX_PARM_DEF_DT DATE NOT NULL,
DBA_NM CHAR(25) NOT NULL,
CITY_NM CHAR(25) NOT NULL,
INDUS_GRP_NM CHAR(20) NOT NULL,
INDUS_CD CHAR(3) NOT NULL,
STATE_CD CHAR(2) NOT NULL,
SE_NM CHAR(40) NOT NULL,
SALE_CHAN_IN CHAR(1) NOT NULL,
SE_TYPE_CD CHAR(2) NOT NULL,
STOP_STA_CD CHAR(2) NOT NULL,
EDC_BP_ADJ_RT INTEGER NOT NULL,
CPC_BP_ADJ_RT INTEGER NOT NULL,
AEBA_ADJ_UPDT_RT DECIMAL(5, 2) NOT NULL,
MER_EDGE_UPDT_RT DECIMAL(5, 2) NOT NULL,
SWAY_STOP_IN CHAR(1) NOT NULL,
LOAD_STA_CD CHAR(1) NOT NULL,
SE_OPEN_DT DATE NOT NULL,
PRO_EDC_BP_ADJ_NO INTEGER NOT NULL,
PRO_CPC_BP_ADJ_NO INTEGER NOT NULL,
STOP_EDC_BP_ADJ_NO INTEGER NOT NULL,
CHAN_CD CHAR(5) NOT NULL,
STOP_CPC_BP_ADJ_NO INTEGER NOT NULL,
PRO_EDC_RT_DRCT_CD CHAR(1) NOT NULL,
PRO_CPC_RT_DRCT_CD CHAR(1) NOT NULL,
TOC_SE_NO CHAR(10) NOT NULL,
CAP_SE_NO CHAR(10) NOT NULL,
MAP_SE_NO CHAR(10) NOT NULL
) END-EXEC.
END-EXEC
EVALUATE SQLCODE
WHEN ZERO
MOVE C-ONE TO A-OUTPUTS
WHEN C-803
MOVE C-ONE TO C-DUP-RECS
WHEN OTHER
MOVE P-T9000 TO WS99-ERROR-PARA-NAME
MOVE C-SE-DETAIL TO WS99-ERROR-OBJ-NAME
MOVE SPACES TO WS99-ERROR-FIL-ERR-CD
MOVE C-DB2 TO WS99-ERROR-ERR-TYP
MOVE C-INSERT TO WS99-ERROR-FUNCTION
MOVE SQLCODE TO WS-SQLCODE
PERFORM 9000-ABEND
END-EVALUATE
.
Thanks in advance. _________________ Have a Great Day.
Thanks & Regards,
Jai
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Wed Sep 10, 2003 10:07 am Post subject:
Jai,
I notice that you have a varchar column AEBA_MER_IN in your table declaration.It is defined as varchar(1) ? which is weird. Whenever you are trying to insert a varchar column you need to populate the length of the field.The definition of your varchar column would be as follows
You need to move the length of the string you are trying to insert into the length field.If you haven't done that your insert string length is less by 2 bytes.
I moved the Length of the Varchar also to correcponding length variable
MOVE WEB1-AEBA-FLG TO S-AEBA-MER-IN-TEXT
COMPUTE S-AEBA-MER-IN-LEN = FUNCTION LENGTH(WEB1-AEBA-FLG)
But I am getting the Same Error of -408 SQLCODE while Binding.
I also tried with hardcoring all the values to the table while inserting, except this varchar variable.
I was getting SQLCODE=-404 which means:
****************
-404 THE SQL STATEMENT SPECIFIES A STRING THAT IS TOO LONG
Explanation: An INSERT, UPDATE, CALL, VALUES INTO, SET, parameter, host
variable, or transition variable statement specifies a value that is
longer than the maximum length string that can be stored in the target
column.
System Action: The statement cannot be executed.
Programmer Response: Check the length of the target column, parameter,
host variable or transition variable and correct the program or SQL
statement so that the length of the string does not exceed that maximum.
For example, you could use the SUBSTR function to shorten the string.
****************
Please let me know.
Thanks in advance. _________________ Have a Great Day.
Thanks & Regards,
Jai
Joined: 26 Nov 2002 Posts: 12376 Topics: 75 Location: San Jose
Posted: Wed Sep 10, 2003 12:53 pm Post subject:
Jai,
Is WEB1-AEBA-FLG defined as PIC X(1) or something else?? Since you are using the COMPUTE to calculate the length , it will give you field definition length but not the actual length.
Let us say you have defined WEB1-AEBA-FLG as Pic x(10) but it only contains 1 alphabet
When you say
Code:
COMPUTE S-AEBA-MER-IN-LEN = FUNCTION LENGTH(WEB1-AEBA-FLG)
You will get the length as 10 but not 1
Since your varchar column can contain only 1 character , Move +1 to S-AEBA-MER-IN-LEN or change the definition of WEB1-AEBA-FLG to Pic x(1)
WEB1-AEBA-FLG is defined as PIC X(1) and it is field coming from the Flat File.
S-ACT-SUBOR-SE-CT is the DCLGEN Variable and it is declared as S9(11)V Comp-3
I tried to move the File variable(declared as 99999) to some working storage variable declared as S9(11)V comp-3 and then tried to move that to DCLGEN Variable, but I was getting the same error -408.
I was also getting -310 and -311 for other context which was similar insert into the table from the Flat Files. I have to look into that as it is due to the decimal values in the files.
Explanation is as follows:
******************
-310 DECIMAL HOST VARIABLE OR PARAMETER number CONTAINS NON-DECIMAL DATA
Explanation: DB2 received nondecimal data from either an application (in
the form of a host variable), function or a stored procedure (in the form
of a parameter that was passed to or from function or a stored procedure).
number Identifies either the host variable number (if the message is
issued as a result of a FETCH, INSERT, UPDATE, DELETE, SELECT,
VALUES INTO, or SET assignment statement statement), or the
parameter number (if the message is issued as the result of the
invocation of a function, or a CALL statement).
System Action: The statement cannot be processed.
Programmer Response: Correct the application program or stored procedure.
Ensure that all decimal variables or parameters contain valid System/370
packed decimal numbers.
***********
-311 THE LENGTH OF INPUT HOST VARIABLE NUMBER position-number IS
NEGATIVE OR GREATER THAN THE MAXIMUM
Explanation: When evaluated, the length specification for input host
string variable, whose entry in the SQLDA is indicated by position-number,
was negative or greater than the maximum.
System Action: The statement cannot be executed.
Programmer Response: Correct the program to ensure that the lengths of
all host string variables are not negative or that they are not greater
than the maximum allowed length.
**************
Please let me know. _________________ Have a Great Day.
Thanks & Regards,
Jai
Thanks a lot! Kolusu. I really missed that Varchar and I remembered only when U told me about that.
I want to ask u for finding the Length of the Variable if it is declared as x(100) and want to find the length.
If I say FUNCTION LENGTH(VAR1), it will return the declaration length.
And if VAR1 is declared as X(100) and value is only of 10 chars.How to find the length.Please let me know as my another program contains varchar(100) and I have to insert that also.
That -408 SQLCODE Problem in Bind is solved. That was due to the date Field from File which I was moving directly to DCLGEN Variable and then included in insert statement.
But instead of that I tried with moving that DCLGEN Date Group Variable to one working Storage variable declared as X(10) and then tried to move that Working Storage Variable to the column of the Table.
It was bound now.
But when I try to run. It is giving -310 and -180 and -181.
I declared the date as yyyy-mm-dd in the group variable(DCLGEN) and tried to move it from the file.
Then I hardcored the date values and hardcored the decimal declared fields, it is inserting into the table..
The Explanations of the abend are:
********
-310 DECIMAL HOST VARIABLE OR PARAMETER number CONTAINS NON-DECIMAL
DATA
Explanation: DB2 received nondecimal data from either an application
(in
the form of a host variable), function or a stored procedure (in the
form
of a parameter that was passed to or from function or a stored
procedure).
number Identifies either the host variable number (if the message
is
issued as a result of a FETCH, INSERT, UPDATE, DELETE,
SELECT,
VALUES INTO, or SET assignment statement statement), or the
parameter number (if the message is issued as the result of
the
invocation of a function, or a CALL statement).
System Action: The statement cannot be processed.
Programmer Response: Correct the application program or stored
procedure.
Ensure that all decimal variables or parameters contain valid
System/370
packed decimal numbers.
***********
*******
-180 THE DATE, TIME, OR TIMESTAMP VALUE value IS INVALID
Explanation: The length or string representation of a DATE, TIME, or
TIMESTAMP value does not conform to any valid format.
The value can contain one of the following:
o For a host variable, the position number of the input host
variable.
If the position number cannot be determined, a blank is displayed.
o For a character string constant, the character string constant.
The
maximum length that is displayed is the length of SQLERRM.
o For a character column, the column name. If the column is a VIEW
column and it has a corresponding base column, the VIEW column
name is
displayed. If the column is a VIEW column but it does not have a
corresponding base column, a string of '*N' is displayed.
************
-181 THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID
DATETIME VALUE
Explanation: The string representation of a datetime is not in the
acceptable range or is not in the correct format. The proper ranges
for
datetime values are as follows:
****************
Please let me know more about it so that I can resolve the problem and I will then take out the hardcored value and will insert from the File.
Thanks in advance. _________________ Have a Great Day.
Thanks & Regards,
Jai
MOVE WEB2-STOP-COMMENTS TO P-STOP-CMNT-TX-TEXT
COMPUTE P-STOP-CMNT-TX-LEN =
FUNCTION LENGTH(P-STOP-CMNT-TX-TEXT)
Please let me know if this(above one) will give the correct result.
The Problem of the Program(For which I posted) is now at the run time and the error which is coming is as follows:
********************
An invalid sign was detected in a numeric edited sending
field in MASTTB1 on line number 4757.
An attempt has been made to move a signed numeric
edited field to a signed numeric or numeric edited
receiving field in a MOVE statement, but the sign
position in the sending field contained an invalid sign
character for the corresponding PICTURE.
Make sure that the program variables in the failing
statement have been set correctly.
******************
I tried to do the Numeric Test also. I am getting the error even after that.
Please let me know.
Thanks in advance. _________________ Have a Great Day.
Thanks & Regards,
Jai
Actually, there are 10 huge files(similar in structure) containing around 6 billion records in total which has to be inserted into different tables according to business logic. I am inserting it through Batch-DB2 Program as there are some business logic to be calculated and implemented. Somebody told that it is better to put it into file and there is BMC Load through which again it can be loaded to tables.
This Process will repeat only once in a year.
Please suggest me which is better and more about BMC Load.
As all 10 files are similar in structure and same business logic to be implemented to insert into the tables, I tried with one file first and it inserted it with RC=0, but when I tried to implement the same for all 10 files simultaneously, it is giving S722 Abend.
Please let me know how to resolve it.
At last I have given like the below one in JCL.
Explanation: One of the following output limits was exceeded:
o The output limit specified by the OUTLIM keyword on the SYSOUT DD
statement
o The job output limit specified in the LINES and CARDS parameters of
the JES2 JOBPARM statement
o The job output limit specified in the BYTES, CARDS, LINES, and PAGES
parameters on the STANDARDS initialization statement or the JES3 MAIN
statement
System Action: The system ends processing of the job step.
Programmer Response: Check for input/output (I/O) loops. Verify that the
parameter value does not conflict with any installation requirements. If
no errors are found, increase the value in the statement. Then run the job
again.
Source: JES
******************************
Please let me know all your valuable suggestions as soon as possible.
Thanks in advance _________________ Have a Great Day.
Thanks & Regards,
Jai
Further to the Abend S722, the SQLCODE was -904(i.e for Unavailable Resource). Please let me know.
Explanation is as follows:
******************
Explanation: The SQL statement could not be executed because resource
'resource-name' of type 'resource-type' was not available at the time
for
the reason indicated by 'reason-code'. Refer to Table 3 in Appendix B,
"Problem determination" in item PROBLEM_DETERMIN for an explanation of
resource type codes. Refer to "DB2 Codes" for an explanation of the
given
reason code.
******************
Please let me know ur precious suggestions as soon as possible.
Thanks in advance. _________________ Have a Great Day.
Thanks & Regards,
Jai
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
