Stored Proc failure erratic 3604 "Cannot insert a Null va

[radhabalan]

Stored Procedure erratic failure with 3604 "Cannot insert a Null value into a NOT NULL field".

Below is portion( only the failure step is copied below )
of a stored procedure that fails intermittently on a V2R.04.01.01.12 ( REL 04.01.01.36 ) Machine.

The failure occurs with no definite sequence or pattern. The failure is random so to test it I copied the Call statement in Query Man executed continuously. Call to this proc was also done using a JDBC App program ( in a For loop ) when the sequence of failure was different ( somtimes failed in the first round, sometimes 17th so on...)
When the COL1 attribute is changed to make it nullable ( took out NOT NULL ) to determine whether the SELECT is making NULL strings there was none inserted into the table!!!!.

Any suggestions or views or any similar previous experience regarding the above error.
Please revert back with any solutions/views/thoughts you have on this. Thanks in advance.


REPLACE PROCEDURE RandTestProc ( )
BEGIN

DECLARE VarUser CHAR(9) ;
DECLARE VarPasswd CHAR(13) ;
DECLARE VarModUser VARCHAR(200);


SELECT 'axt'||TRIM(RANDOM( 10, 100)) || TRIM(RANDOM( 10,100 )) || TRIM(RANDOM( 10,100))||SUBSTR(DATE (FORMAT 'YYYYMMDD') ,5,2)||SUBSTR(DATE (FORMAT 'YYYYMMDD') ,7,2) INTO :VarPasswd;
INSERT INTO PwdFile( col1 ) VALUES ( :VarPasswd );


END;

DDL for the PwdFile Table given below.

CREATE SET TABLE PwdFile ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL
(
Col1 CHAR(13) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
)
UNIQUE PRIMARY INDEX ( Col1 );

 

[ns1909]

Try using Coalesce function to avoid generation of NULL values -

SELECT COALESCE (('axt'||TRIM(RANDOM( 10, 100)) || TRIM(RANDOM( 10,100 )) || TRIM(RANDOM( 10,100))||SUBSTR(DATE (FORMAT 'YYYYMMDD') ,5,2)||SUBSTR(DATE (FORMAT 'YYYYMMDD') ,7,2)),0) INTO :VarPasswd;
INSERT INTO PwdFile( col1 ) VALUES ( :VarPasswd );

 

[SirThinksALot]

I have experience a similar problem using tpump. Have you been able to resolve the issue? Please let me know of the reason/a solution/workaround if you have one.
Thanks

 

[tdatgod]

Hi,
I have seen this somewhere before but I can't remember where.

you do realize that random(10,100) can return 100.

SELECT TRIM(RANDOM( 10, 100));

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

Trim(BOTH FROM Random(10,100))
------------------------------
100



if any of your || conditions retun 100 you will have 14 characetrs generated

'atx' '100' '67' '75' '12' '03'

I don't know if this is the cause of the problem Since I don't know what happens if you try to stuff 14 characters into a 13 character variable.

Worse case you could have 16 characters.

'atx' '100' '100' '100' '12' '03'

I wonder if this is where your NULL value is coming from.




-----