When submitting a procedure via CL program, i do not get any abnormal end message.

1160 pts.
Tags:
CL programming
CL programs
CLP
Stored Procedures
When submitting a procedure via CL program, i do not get any abnormal end message even though the procedure has not not completed normally, is there anyway by which we can find the cause of the failure of the completion of the procedure normally.

Software/Hardware used:
procedure CLP systemi as400

Answer Wiki

Thanks. We'll let you know when a new response is added.

Are you looking for the error msg? The joblog will have the cause of the failure.
On the SBMJOB command, change the log level to produce a joblog upon completion.
Prompt SBMJOB, press F10, scroll down twice and you will see Message logging. Change the Level to 4, Severity to 0, and Text to *Seclvl.

********************************

You might find something useful <a href=”http://fixunix.com/ibm-as400/105953-how-call-cle-clp.html”>here</a>. This page has a solution that deals with passing parameters, as well as a link that might provide additional help.
Good Luck!
Flame

Discuss This Question: 12  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • JohnsonMumbai
    I have done the same, while submission of the cl is successful ie to call the procedure. The failure of the procedure is not reflected in the joblog.
    1,160 pointsBadges:
    report
  • TomLiotta
    The failure of the procedure is not reflected in the joblog. Then how can you tell that it failed? There isn't near enough information here to determine a cause. We at least need to see the SBMJOB command that you run as well as the definition of any entry parameters from the called program. Tom
    125,585 pointsBadges:
    report
  • JohnsonMumbai
    The CL is actually submitting multiple SQL procedures, which updates a sequence of files one by one, after each update a log file is updated indicating the update has completed, whenever there is a failure the complete sequence of update is not completed in the log file and is stopped midway is when we realise the sql procedure submitted has ended abnormally.
    1,160 pointsBadges:
    report
  • TomLiotta
    The CL is actually submitting multiple SQL procedures... CL can't "submit" procedures. CL can only submit programs. And CL has no support for SQL procedures, even interactively -- a HLL program must be called to execute SQL procedures (with exceptions for complex SQL CLI coding in V5R4 and above). Maybe there is confusion over some of the terms. Therefore, the only reasonable interpretation is that your CL is calling (or submitting) a program that has embedded SQL. The embedded SQL may issue a SQL CALL to a SQL stored procedure. If the CL calls the program, then any errors should be shown in the joblog of the job that runs the CL. If the CL submits the call to the program, then any errors should be shown in the joblog of the submitted job. If no errors are found in the job that runs the program, then you need to ask the programmer why he or she did not log errors in the joblog. The programmer is the only one who can explain why there was no coding to log errors. It seems that you are simply calling a program that needs to be changed to include better logging. Tom
    125,585 pointsBadges:
    report
  • JohnsonMumbai
    The sql procedure is created by using the strsql command and thereafter using the create procedure command within the sql session and thereafer the sql commands that need to be executed are entered within the procedure. The procedure is saved as a program object with CLE attribute. This program object is then called by a CL program. I guess you are right the programmer who has written the sql commands needs to log the errors if any within the sql procedure.
    1,160 pointsBadges:
    report
  • TomLiotta
    The procedure is saved as a program object with CLE attribute. This program object is then called by a CL program. AFAIK, the resulting CLE program object cannot be directly called successfully from a CL program. It could only be called from a SQL program that was compiled with embedded SQL to run the SQL CALL statement. You can't use the CL CALL command for this. There are parts that need to be built into the program by the SQL pre-compiler in order to support the SQL CALL statement properly. There is no SQL pre-compiler for CL. Your CLE procedure will need to be called from RPG, COBOL, C or another HLL that has SQL compiler support. Tom
    125,585 pointsBadges:
    report
  • JohnsonMumbai
    We have been submitting the CLE pgm by calling it in a CL program, it work fine, only issue as mentioned is when the CLE pgm fails there is no log generated. From what i understand from you if i call the CLE pgm from an RPG pgm and if there is an error in the CLE pgm i should be able to get a joblog informing of the error.? Please confirm.
    1,160 pointsBadges:
    report
  • TomLiotta
    Because SQL procedures are created as *PGM objects, they are technically capable of being executed with a CL CALL command. However, in doing so, they are being run outside of the SQL environment. You're going to need a serious DB2 SQL developer to determine where any results might end up. If your procedures need to return SQL status information, there is no active SQL environment to receive it. Status info would simply be lost in my view of things. Wrap your SQL procedures inside of SQL CLI calls or call them from programs compiled with embedded SQL. I can't think of any other possibilities for tracking whatever is happening. If you don't have the SQL Development Kit to allow compiling HLL SQL programs, you can still code for SQL CLI or issue SQL CALL statements in REXX. (But REXX SQL CALL statements don't support host variables.) I'd love to see someone else provide a better answer. Only thing I can suggest is to call the procedures properly if you want proper results. Tom
    125,585 pointsBadges:
    report
  • TomLiotta
    re Flame's updated answer... When I originally replied to that forum a couple years ago, it was about a slightly different problem. However, that thread does indeed contain a possible useful item -- using a QM query to issue the CALL. Since a QM query provides a mostly valid SQL environment, it might help in returning and recording SQL status info as messages in the joblog. And that's apparently the crux of this question. Calling the proc directly from CL potentially loses SQL status info. But issuing STRQMQRY to CALL the proc might be a nicely valid answer here. Nice catch! Tom
    125,585 pointsBadges:
    report
  • JohnsonMumbai
    Hi Tom, Tried strmqmqry, however was able to find how to store the output of the sql. Can you give us a sample QMQRY. Await your reply. Johnson
    1,160 pointsBadges:
    report
  • NullFields
    It seems to me that building a stored procedure to generate a program that is then called directly is a long way around to solving a problem. Is it that the only languages you are familiar with are CL and SQL? It's interesting, but limiting (like handling of input/output parameter types that CL does not have a defintion for). To work with QMQRY, use the menu STRQM, then option 1 to get into generating a query. Option 10 to change your user options. Once a query is defined, you can execute it from a CLP using the command STRQMQRY. The command has options to pass in variables to make it a little more dynamic. So, you would probably have two QMQRY's. The first would define the procedure. That one would contain the CREATE PROCEDURE statement you used to generate the *PGM object. That would only need to be done to save the source of the procedure better than your options with STRSQL. The second would contain the CALL PROCEDURE statement that would be equivalent to executing the procedure from within STRSQL.
    880 pointsBadges:
    report
  • NullFields
    Almost forgot.... You can store your queries in a source member (length of source area=79, total record length 91). Then use the command CRTQMQRY to generate the executable query object (*QMQRY).
    880 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following