I am trying to create a stored procedure in iNav that will read a file on my
i5 and update our price file.
This is something I have to do on a somewhat regular basis, and I was trying
to create a stored procedure to help me with it.
I usually receive new price sheets from sales, via a spreadsheet which I
upload to our i5, then run an SQL script to load the data into the price
master.
I'm getting stuck with trying to figure out how to create the Proc and have
it use a variable name for the input price sheet and library.
Is there a way to do this?
Here's the SQL that was generated:
CREATE PROCEDURE CCSDTATEST.INS_DOPPRCM_LOAD_FILE (
IN PRCSHT CHAR(10) ,
IN BEGDTEYMD CHAR(6) ,
IN PRCSHTLIB CHAR(10) )
LANGUAGE SQL
SPECIFIC CCSDTATEST.INS_DOPPRCM_LOAD_FILE
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
INSERT INTO CCSDTATEST . DOPPRCM
( KEY1SRC , KEY1 , KEY2SRC , DTELSTUPD , REVDTE , BEGDTE , ENDDTE ,
PRCACT , QTYACT , STDPKGFLG , PRCBY , SETPRC , METHOD , KEY2 , PRCOVR1 )
SELECT 'I' , ''' || PRCSHT || ''' , 'A' ,
( SELECT CURMDY FROM IQDATE . CURDATE ) ,
( SELECT CURMDY + 1 FROM IQDATE . CURDATE ) ,
BEGDTEYMD , '123149' ,
'P' , 'N' , 'N' , 'V' , 'N' , 'N' ,
ITEMID , PRICE FROM PRCSHTLIB . PRCSHT ;
COMMENT ON SPECIFIC PROCEDURE CCSDTATEST.INS_DOPPRCM_LOAD_FILE
IS 'Insert COPS Price Sheet data from Load File' ;
An example call and resulting error.
CALL CCSDTATEST.INS_DOPPRCM_LOAD_FILE('DCV1102','110217','PRICES11')
SQL State: 42704
Vendor Code: -204
Message: [SQL0204] PRCSHT in PRCSHTLIB type *FILE not found. Cause . . . . .
: PRCSHT in PRCSHTLIB type *FILE was not found. If the member name is
*ALL, the table is not partitioned. If this is an ALTER TABLE statement and
the type is *N, a constraint or partition was not found. If this is not an
ALTER TABLE statement and the type is *N, a function, procedure, trigger or
sequence object was not found. If a function was not found, PRCSHT is the
service program that contains the function. The function will not be found
unless the external name and usage name match exactly. Examine the job log
for a message that gives more details on which function name is being
searched for and the name that did not match. Recovery . . . : Change the
name and try the request again. If the object is a node group, ensure that
the DB2 Multisystem product is installed on your system and create a
nodegroup with the CRTNODGRP CL command. If an external function was not
found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION
statement exactly matches the case of the name exported by the service
program.
midrange.com
