Retrieve source from stored procedure on AS400

[voicucosmin90]

Hi guys,

I have a stored procedure created with iSeries Navigator - and on AS400 I have the object generated and I verified with sql script on iseries navigator - the results are good,
The question is how can I make source retrieve on AS400 for this code procedure - I have the object generated with Type *PGM, Attribut CLE (verified with wrkobj) - I saw that are other sources in my library in QSQLSRC and I want to make retrieve. Thanks

[TedHolt]

Re: Retrieve source from stored procedure on AS400

Try the QSYS2.GENERATE_SQL stored procedure.

http://www.itjungle.com/fhg/fhg111214-story01.html

https://www.ibm.com/developerworks/c...()%20procedure

[voicucosmin90]

Re: Retrieve source from stored procedure on AS400

Thanks, I try now but I need some help, this is the situation:


I have the procedure on iSeries navigator on schemas -> Procedures - type SQL
and on iSeries I have a member - type SQL in QSQLSRC, mylib. I don't know exactly how it's possible to make this - to see this in PDM on iSeries

[DeadManWalks]

Re: Retrieve source from stored procedure on AS400

please have a look at' Run SQL Statements (RUNSQLSTM)

[voicucosmin90]

Re: Retrieve source from stored procedure on AS400

I think with Run sql statements it's only possible to specifies the source file that contains the SQL statements to be run.


The situation is something like this:
I already have a procedure created directly in iSeries System Navigator -> databases -> schemas -> procedures, after creation in iSeries System Navigator it has been created the object on my libarry on iSeries( I mean on AS400 -> verified with wrkobj)
--> I want to retrieve the source code for this stored procedure to QSQLSRC / MYLIB in a member, SQL Type & CLE Attribut. It is possible or I have to write in SEU the code?

Briefly, I want to have a member with the source code for my procedure

[Michael Jones]

Re: Retrieve source from stored procedure on AS400

A couple of options for you...

First option is iSeries System Navigator allows you to right click an SQL object, and from the popup menu, choose to generate the SQL source. That will save you from retyping the code. You'd then have to copy/paste/save the generated source code into an editor that is source physical file compatible, like SEU or RDi.

Second option is to use the GENERATE_SQL procedure: GENERATE_SQL
This option is capable of generating the source and depositing the result directly into a source member in a single step.

You should make the source member type = SQL, although you don't have to. Some SQL objects get converted to ILE-C language, and when compiled show a program attribute of CLE as a result. You might want to use source member types like SQLFUNC (SQL function), SQLPROC (SQL procedure), SQLTABLE (SQL table), SQLVIEW (SQL view), etc. to be more descriptive.

If you use RUNSQLSTM to compile your SQL objects, it doesn't care what source member type you've applied to the source member.

[voicucosmin90]

Re: Retrieve source from stored procedure on AS400

Thank Michael for the answer, I've never used GENERATE_SQL before

I looked and I tried something like this:
CALL GENERATE_SQL('MY_OBJECT_NAME', 'MY_OBJECT_LIBRARY', 'PROCEDURE', 'MY_MEMBER_NAME', 'QSQLSRC', 'SRCLIB'); --> I ran this on run sql scipts on iseries navigator.

MY_OBJECT_NAME=MY_MEMBER_NAME - I want to have a member with the same name as my store procedure.
SRCLIB/QSQLSRC - here I want to retrieve my source and have a member with the same name.

--> I have " Statement ran successfully, with warnings" but I don't find my member on AS400 on SRCLIB/QSQLSRC.

What am I wrong?

[jackc]

Re: Retrieve source from stored procedure on AS400

Source physical file member names are limited to 10 characters.

If you specified 'MY_MEMBER_NAME' as the target, I believe the system uses the first 5 characters and a sequence number, so the output will be stored in member 'MY_ME00001'

[voicucosmin90]

Re: Retrieve source from stored procedure on AS400

Yes, this is it right, I want to say something like this, it was only for the example:
CALL GENERATE_SQL('NAME1', 'OBJLIB', 'PROCEDURE', 'NAME1', 'QSQLSRC', 'SRCLIB');

where name1 is the object name and also source name
and OBJLIB - is the object library
QSQLSRC- the source file where I want to retrieve my source
SRCLIB- the library where I want to retrieve my source.

and PROCEDURE - object type

.. but doesn't work

[B.Hauser]

Re: Retrieve source from stored procedure on AS400

I assume it is a bug, ... but the source member must exist.

Birgitta

[Michael Jones]

Re: Retrieve source from stored procedure on AS400

I'd not used GENERATE_SQL before, but just tried it. I came to the same conclusion as Birgitta, that the source member must exist prior to running GENERATE_SQL. i.e. perform an ADDPFM CL command first to create the member, and then run GENERATE_SQL.

Also, you need to adjust some of the parameters to match this:

CALL GENERATE_SQL('OBJECT_NAME', 'OBJECT_LIBRARY', 'OBJECT_TYPE', 'SOURCE_FILE_NAME', 'SOURCE_FILE_LIBRARY', 'SOURCE_MEMBER_NAME');

[Michael Jones]

Re: Retrieve source from stored procedure on AS400

One more thing -

When I ran a test, by inspecting the job log of the job in which GENERATE_SQL ran, it was clear that GENERATE_SQL wanted the source file member to already exist.

The IBM i platform, to my knowledge, is probably by far the best system on the planet, at providing detailed error message feedback, and it does so in the "job log". Make sure you heavily leverage looking at the job log when you have errors. Navigator has a menu pull down option to access the job log.

[jackc]

Re: Retrieve source from stored procedure on AS400

Yes, this is it right, I want to say something like this, it was only for the example:
CALL GENERATE_SQL('NAME1', 'OBJLIB', 'PROCEDURE', 'NAME1', 'QSQLSRC', 'SRCLIB');

where name1 is the object name and also source name
and OBJLIB - is the object library
QSQLSRC- the source file where I want to retrieve my source
SRCLIB- the library where I want to retrieve my source.

and PROCEDURE - object type

.. but doesn't work

As Micheal Jones replied earlier, your positional parameters are not in the correct sequence. Same statement with parameters in the correct sequence should work (assuming the target source member exists):

CALL GENERATE_SQL('NAME1', 'OBJLIB', 'PROCEDURE', 'QSQLSRC', 'SRCLIB', 'NAME1');

1 DATABASE_OBJECT_NAME
2 DATABASE_OBJECT_LIBRARY_NAME
3 DATABASE_OBJECT_TYPE
4 DATABASE_SOURCE_FILE_NAME
5 DATABASE_SOURCE_FILE_LIBRARY_NAME
6 DATABASE_SOURCE_FILE_NAME

I'd recommend adding STATEMENT_FORMATTING_OPTION=>'0' to prevent the procedure from inserting end of line characters that aren't needed in source physical file members.

CALL GENERATE_SQL('NAME1', 'OBJLIB', 'PROCEDURE', 'QSQLSRC', 'SRCLIB', 'NAME1',STATEMENT_FORMATTING_OPTION=>'0');

[voicucosmin90]

Re: Retrieve source from stored procedure on AS400

Thanks a lot, I just did it with your help , was very helpful, it works!!