Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SQLCODE = -444. Where to specify the LOAD library

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Dec 15, 2009 3:28 pm    Post subject: SQLCODE = -444. Where to specify the LOAD library
Reply with quote

Hi,

Following is JCL, got it from this link. I made changes to work till the last step.

Code:
//CONTK2TS JOB CLASS=K,
//         MSGCLASS=A,MSGLEVEL=(1,1),NOTIFY=&SYSUID
//*                                                                     0000000
//*********************************************************************
//*                                                                   *
//*  THIS INFORMATION CONTAINS SAMPLE APPLICATION PROGRAMS IN         *
//*  SOURCE LANGUAGE, WHICH ILLUSTRATE PROGRAMMING TECHNIQUES         *
//*  ON VARIOUS OPERATING PLATFORMS. YOU MAY COPY, MODIFY, AND        *
//*  DISTRIBUTE THESE SAMPLE PROGRAMS IN ANY FORM WITHOUT             *
//*  PAYMENT TO IBM, FOR THE PURPOSES OF DEVELOPING, USING,           *
//*  MARKETING OR DISTRIBUTING APPLICATION PROGRAMS CONFORMING        *
//*  TO THE APPLICATION PROGRAMMING INTERFACE FOR THE OPERATING       *
//*  PLATFORM FOR WHICH THE SAMPLE PROGRAMS ARE WRITTEN. THESE        *
//*  EXAMPLES HAVE NOT BEEN THOROUGHLY TESTED UNDER ALL               *
//*  CONDITIONS. IBM, THEREFORE, CANNOT GUARANTEE OR IMPLY            *
//*  RELIABILITY, SERVICEABILITY, OR FUNCTION OF THESE PROGRAMS.      *
//*  THE SAMPLE PROGRAMS ARE PROVIDED "AS IS", WITHOUT WARRANTY       *
//*  OF ANY KIND.  IBM SHALL NOT BE LIABLE FOR ANY DAMAGES            *
//*  ARISING OUT OF YOUR USE OF THE SAMPLE PROGRAMS.                  *
//*                                                                   *
//*********************************************************************
//*                                                                   *
//*      CUSTOMIZATION NOTES:                                         *
//*                                                                   *
//*      CHANGE THESE DATASET NAMES TO SHOP SPECIFIC NAMES            *
//*                                                                   *
//*********************************************************************
//*                                                                     0000000
//JOBLIB   DD  DISP=SHR,DSN=DB2A.TESTLIB                                0000000
//         DD  DISP=SHR,DSN=DB2A.SDSNLOAD                               0000000
//         DD  DISP=SHR,DSN=CEE.SCEERUN                                 0000000
//*                                                                     0000000
//         SET MYLOAD=USER.TESTLIB
//*                                                                     0000000
//*                                                                     0000000
//*   ALSO, CHANGE 'WLMENV1' TO YOUR WLM ENVIRONMENT NAME               0000000
//*   ALSO, CHANGE 'V81A' TO YOUR SSID                                  0000000
//*   ALSO, MAKE SURE THE PROGRAM AND PLAN NAME FOR DSNTEP2 IS CORRECT  0000000
//*                                                                     0000000
//*                                                                     0000000
//*********************************************************************
//*      END OF CUSTOMIZATION NOTES                                   *
//*********************************************************************
//*                                                                     0000000
//*********************************************************************
//*   IN STREAM PROCS FOR ASSEMBLE AND LINKEDITS                        0000000
//*********************************************************************
//ASM      PROC
//A        EXEC PGM=ASMA90,COND=(4,LT),PARM='TERM,OBJECT,RLD,LIST(121),
//             ALIGN,ESD,NODECK,PC(DATA)'
//SYSLIB   DD   DISP=SHR,DSN=CEE.SCEEMAC                                0000000
//         DD   DISP=SHR,DSN=SYS1.MACLIB
//SYSLIN   DD   DSN=&&OBJECT,DISP=(MOD,PASS),UNIT=SYSDA,
//             SPACE=(32000,(30,30)),
//             DCB=(RECFM=FB,LRECL=80,BLKSIZE=3200)
//SYSTERM  DD   SYSOUT=*
//SYSPRINT DD   SYSOUT=*
//SYSUT1   DD   UNIT=VIO
//         PEND
//********
//LINKEDIT PROC MBR=
//LK       EXEC PGM=IEWL,PARM='AC(0),AMODE(31),RMODE(ANY),NOLET,REUS(RE
//             NT)',COND=(4,LT)
//SYSLIB   DD   DISP=SHR,DSN=&MYLOAD
//         DD   DISP=SHR,DSN=CEE.SCEELKED
//SYSLMOD  DD   DISP=SHR,DSN=&MYLOAD(&MBR)
//SYSPRINT DD   SYSOUT=*
//SYSLIN   DD   DISP=(OLD,DELETE),DSN=&&OBJECT
//         PEND
//*
//*********************************************************************
//*      ASSEMBLE THE USER DEFINED FUNCTION                           *
//*********************************************************************
//CONTK2TS EXEC PROC=ASM
//A.SYSIN  DD   *
CONTK2TS CEEENTRY AUTO=WORKLENG,MAIN=NO,PLIST=OS,BASE=R11,NAB=YES       00740000
*                                                                       00750000
         USING WORKAREA,R13
         MVC   WORKMAC(CONSTCKZ),CONSTCK   SET MACRO CONSTANT
         LR    R6,R1           POINTER TO PASSED LIST OF PARMS
         USING PLIST,R6
**
**       CONVERT THE CONSISTENCY TOKEN (PARM 1) TO A STCK
**
         L     R2,PTR_P1       FIRST PARM TO UDF (CONTOKEN)
         USING PARM1,R2
         LM    R7,R8,PARM1D          PICK UP THE VALUE
         DROP  R2                    PARM1
         MH    R8,=H'8'              * 8
         STM   R7,R8,WORKSTCK
         LG    R7,WORKSTCK
         MGHI  R7,8                  * 8
         STG   R7,WORKSTCK           TOKEN IS NOW A STCK
**
**       NOW, CONVERT STCK TO DECIMAL DIGITS
**
         STCKCONV STCKVAL=WORKSTCK,CONVVAL=WORKCONV,TIMETYPE=DEC,      X
               DATETYPE=YYYYMMDD,MF=(E,WORKMAC)
**
**       REARRANGE OUTPUT OF STCKCONV TO DESIRED FORMAT FOR EDIT
**
         LM    R14,R15,WORKCONV
         MVC   WORKCONV(4),WORKCONV+8
         STM   R14,R15,WORKCONV+4
**
**       SET EDIT PATTERN AND FORMAT IT
**
         L     R4,PTR_RES             RESULT DESTINATION
         USING RESULT,R4
         MVC   RESULTD(26),EDITPAT
         ED    RESULTD(26),WORKCONV
         DROP  R4
**
**       SET SQLSTATE TO ZEROS
**
         L     R14,PTR_STAT
         MVC   0(5,R14),=CL5'00000'    ALL OK
         XR    R15,R15                 RC=0
         CEETERM RC=(R15)              BUH BYE                          03730000
*********************************************************************** 04590000
*        CONSTANTS                                                    * 04600000
*********************************************************************** 04610000
PPA      CEEPPA  ,                                                      03820000
         LTORG                                                          03830000
**
**       EDIT PATTERN FOR TIMESTAMP
**
EDITPAT  DS    0CL26
         DC    X'21202020',C'-'
         DC    X'2020',C'-'
         DC    X'2020',C'-'
         DC    X'2020',C'.'
         DC    X'2020',C'.'
         DC    X'2020',C'.'
         DC    X'202020202020'
*
CONSTCK  STCKCONV MF=L
CONSTCKZ EQU   *-CONSTCK
*
*********************************************************************** 04590000
*        DSECTS                                                       * 04600000
*********************************************************************** 04610000
*                                                                       04620000
PLIST    DSECT          UDF PARM MAPPING FOR THIS UDF
PTR_P1   DS    A        POINTER TO PARAMETER 1
PTR_RES  DS    A        POINTER TO RESULT FIELD
PTR_P1I  DS    A        POINTER TO PARAMETER1 NULL INDICATOR
PTR_RESI DS    A        POINTER TO RESULT INDICATOR
PTR_STAT DS    A        POINTER TO SQLSTATE
PTR_NAME DS    A        POINTER TO FUNCTION NAME
PTR_SNAM DS    A        POINTER TO SPECIFIC NAME
PTR_MSGT DS    A        POINTER TO MESSAGE TEXT FIELD
*
PARM1    DSECT
PARM1D   DS    0C       PARM1 DATA (8 BYTES)
*
RESULT   DSECT          OUTPUT DATA - 26 BYTES
RESULTD  DS    0C
*
WORKAREA DSECT                                                          04630000
         ORG   *+CEEDSASZ      LEAVE SPACE FOR DSA FIXED PART           04640000
WORKSTCK DS    D
WORKCONV DS    CL16
*
         DS    0F
WORKMAC  DS    XL(CONSTCKZ)
*
         DS    0D
WORKLENG EQU   *-WORKAREA                                               05400000
         CEEDSA  ,             MAPPING OF THE DYNAMIC SAVE AREA         05410000
         CEECAA  ,             MAPPING OF THE COMMON ANCHOR AREA        05420000
         YREGS                                                          05430021
         END                                                            05461000
//*
//***************************************************************
//*   LINKEDIT THE UDF
//***************************************************************
//LINKCHAR EXEC  PROC=LINKEDIT,MBR=CONTK2TS
//*
//********************************************************************* 0000000
//*      CREATE THE UDF                                               * 0000000
//********************************************************************* 0000000
//CRE8STEP EXEC PGM=IKJEFT01,COND=(4,LT)
//SYSPRINT DD   SYSOUT=*
//SYSTSPRT DD   SYSOUT=*
//SYSTSIN  DD   *

  DSN SYSTEM(V81A)
    RUN PROGRAM(DSNTEP2)  PLAN(DSNTEP81)
  END

//*
//SYSIN  DD *

    DROP SPECIFIC FUNCTION CONTK2TS ;
    COMMIT ;

    CREATE FUNCTION
     CONTK2TS( TOKEN SYSIBM.CHAR(8) )
     RETURNS SYSIBM.CHAR(26)                                               0054
     LANGUAGE ASSEMBLE                                                     0054
     SPECIFIC CONTK2TS                                                     0054
     EXTERNAL NAME 'CONTK2TS'                                              0054
     NO COLLID                                                             0054
     ASUTIME NO LIMIT                                                      0054
     STAY RESIDENT YES                                                     0054
     WLM ENVIRONMENT WLMENV1                                               0054
     NO SQL                                                                0054
     FENCED                                                                0054
     PARAMETER CCSID EBCDIC                                                0054
     PARAMETER STYLE DB2SQL                                                0054
     DETERMINISTIC                                                         0054
     RETURNS NULL ON NULL INPUT                                            0054
     NO EXTERNAL ACTION                                                    0054
     NO SCRATCHPAD                                                         0054
     ALLOW PARALLEL                                                        0054
     NO DBINFO                                                             0054
     INHERIT SPECIAL REGISTERS                                             0054
     STOP AFTER SYSTEM DEFAULT FAILURES                                    0054
     PROGRAM TYPE SUB ;                                                    0054

//********************************************************************* 0000000
//*      IVP THE UDF                                                  * 0000000
//********************************************************************* 0000000
//IVPSTEP  EXEC PGM=IKJEFT01,COND=(4,LT)
//SYSPRINT DD   SYSOUT=*
//SYSTSPRT DD   SYSOUT=*
//SYSTSIN  DD   *

  DSN SYSTEM(V81A)
    RUN PROGRAM(DSNTEP2)  PLAN(DSNTEP81)
  END

/*
//*
//*
//SYSIN  DD *
                                                                        00540002
  SELECT
  HEX(CONTOKEN) AS "HEX CONTOKEN" ,
  CAST(CONTK2TS(CONTOKEN) AS CHAR(26)) AS "CONVERTED TO TS"
  FROM SYSIBM.SYSPACKAGE
  WHERE NAME LIKE 'DSN%'
  ORDER BY 2 ;


The part i don't know is, how will the FUNCTION know that load is available in NDEV.DLMF.LOAD.HXSULL library and execute it.

Sushanth
Back to top
View user's profile Send private message

Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Dec 15, 2009 4:24 pm    Post subject:
Reply with quote

Sushanth,

Quote:
-444 USER PROGRAM name COULD NOT BE FOUND

Explanation:

DB2 received an SQL CALL statement for a stored procedure or an SQL
statement containing an invocation of a user-defined function, and found
the row in the SYSIBM.SYSROUTINES catalog table associated with the
requested procedure name. However, the MVS load module identified in the
EXTERNAL_NAME column of the SYSIBM.SYSROUTINES row could not be found.


The stored procedure load module must be placed in a load library
that is included in the STEPLIB DD concatenation in the WLM startup JCL.

I believe this holds good for external function as well.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Dec 15, 2009 4:50 pm    Post subject:
Reply with quote

Thank You Very Much SriHari,

Quote:
The stored procedure load module must be placed in a load library
that is included in the STEPLIB DD concatenation in the WLM startup JCL.


This i didn't know and it worked.

Thank You Very Much Again.

Following are the results using above JCL.
Code:
   +-----------------------------------------------+
   |   HEX CONTOKEN   |      CONVERTED TO TS       |
   +-----------------------------------------------+
 1_| 154A91441F1F9094 | 1994-12-15-11.52.29.402690 |
 2_| 15E0D26B0A3BD3E8 | 1997-07-28-13.13.49.602639 |
 3_| 16508FA1109E0490 | 1999-07-09-12.54.14.011922 |
 4_| 1688DF2B0E7C0038 | 2000-07-01-20.06.27.866624 |
 5_| 1691E8590CF3EFF0 | 2000-08-28-07.58.23.018943 |
 6_| 15F3AA3F1F5CDD64 | 1997-11-25-10.45.27.160693 |
 7_| 16432FAE19A8E360 | 1999-04-15-10.26.21.423501 |



Sushanth
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Tue Dec 15, 2009 5:35 pm    Post subject:
Reply with quote

Glad to know that it worked and thanks for sharing the results.
Back to top
View user's profile Send private message
Ajay Baghel

Active User


Joined: 25 Apr 2007
Posts: 205
Location: Bangalore

PostPosted: Tue Dec 15, 2009 7:26 pm    Post subject: Reply to: SQLCODE = -444. Where to specify the LOAD library
Reply with quote

Quote:
The stored procedure load module must be placed in a load library
that is included in the STEPLIB DD concatenation in the WLM startup JCL
.

Sorry i did not understand, what was done to resolve it. What is WLM startup JCL? Please clarify.

Thank you,
Ajay
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Dec 15, 2009 8:03 pm    Post subject:
Reply with quote

Hi Ajay,

WLM - Work Load Manager.

To start it there is a JCL. In the STEPLIB of that jcl, few datasets will be concatenated and as SriHari said your load module have to be in that library.

If you have access to that library you can copy your load module there.

Sushanth
Back to top
View user's profile Send private message
Ajay Baghel

Active User


Joined: 25 Apr 2007
Posts: 205
Location: Bangalore

PostPosted: Thu Dec 17, 2009 8:56 pm    Post subject: Reply to: SQLCODE = -444. Where to specify the LOAD library
Reply with quote

Thanks Sushanth for clarifying this.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Db2 load job abending in SORT mistah kurtz DB2 4 Mon Jun 05, 2017 3:58 pm
No new posts Cobol upgrade - source code missing f... gthmrj IBM Tools 1 Wed Apr 26, 2017 6:04 pm
No new posts Copy load modules and alias vasanthz IBM Tools 5 Thu Apr 20, 2017 6:31 am
No new posts DB2 SQL Error: SQLCODE=-440, SQLSTATE... kishpra DB2 1 Sat Feb 18, 2017 2:31 am
No new posts Can DFSPARM override SORTKEYS paramet... ashek15 DB2 2 Tue Jan 31, 2017 11:36 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us