IBM Support

ERROR: pg_atoi: error reading "4138613859": Numerical result out of range"

Troubleshooting


Problem

Customer sometimes faced the error message like "pg_atoi: error reading "4138613859": Numerical result out of range" when running a Stored Procedure.

Symptom


*pg.log
2015-01-02 05:47:27.709629 KST [24828] DEBUG: STORED PROCEDURE EXEC:
DELETE FROM SIDM.DM_STOR_STOC_F WHERE STD_DT = $1 - Variables (20150101)
2015-01-02 05:47:28.021035 KST [24828] DEBUG: 0 rows deleted
2015-01-02 05:47:28.023043 KST [24828] DEBUG: STORED PROCEDURE EXEC:
DELETE FROM SIDM.DM_STOR_STOC_F WHERE STD_DT < $1 ||'0101' - Variables (2013)

2015-01-02 06:47:26.356574 KST [24828] DEBUG: InvalidateSharedInvalid: handled cache state reset
2015-01-02 06:47:26.363986 KST [24828] DEBUG: 4138613859 rows deleted
2015-01-02 06:47:26.365667 KST [24828] ERROR: pg_atoi: error reading "4138613859": Numerical result out of range <<---
2015-01-02 06:47:26.400096 KST [24828] DEBUG: STORED PROCEDURE EXEC:
INSERT INTO ADMIN.ETL_LOAD_LOG ( STD_DT ,PRC_ID ,START_DT ,END_DT
,T_TBL_NM ,ETL_ST_DT ,ETL_ED_DT ,SELECT_CNT ,INSERT_CNT ,DELETE_CNT
,UPDATE_CNT ,PRC_TM ,IU_FLG ,PRC_RSLT ,ERR_CODE ,ERR_MSG ) VALUES (
to_char(now(), 'YYYYMMDD') , $1 , $2 ,now() , $3 , $4 , $5 , $6 ,
$7 , $8 , $9 ,date_part('day' , now() - $10 ) *24*60*60 +
date_part('hour' , now() - $11 ) *60*60 + date_part('minute', now() -
$12 ) *60 + date_part('second', now() - $13 ) , $14 ,'FAIL' , $15
, $16 ) - Variables (SP_DM_STOR_STOC_F, 2015-01-02 05:47:28,
DM점재고현황_F, 20150101, 20150101, 0, 0, 0, 0, 2015-01-02 05:47:28,
2015-01-02 05:47:28, 2015-01-02 05:47:28, 2015-01-02 05:47:28, B,
pg_atoi: error reading "4138613859": Numerical result out of range,
pg_atoi: error reading "4138613859": Numerical result out of range)
2015-01-02 06:47:26.644918 KST [24828] DEBUG: 1 rows inserted
2015-01-02 06:47:26.814390 KST [24828] DEBUG: disconnect: host=10.253.41.57 user=SIDM database=SINDW remotepid=26673340 fetype=1

*Stored Procedure
CREATE OR REPLACE PROCEDURE SP_TEST (CHARACTER VARYING(50))
RETURNS CHARACTER VARYING(ANY)
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
V_DEL_CNT INTEGER; <<-- declared available range as result value.

Cause

The result value size in the stored procedure was declared smaller than the result value actually received.

integer range : -2147483647 ~ +2147483648

[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Database","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
17 October 2019

UID

swg21696780