Tips: Run and call System i stored procedures

Use these tips for running and calling System i™ stored procedures.

Running a System i stored procedure

ODBC provides a standard interface for calling stored procedures. The implementation of stored procedures differs significantly across various databases.
This simple example follows the recommended approach for running a System i stored procedure.

1.)Set up a create procedure statement for the stored procedure and create it. The creation of the stored procedure only needs to be done once and it does not have to be done through ODBC. The definition that it provides is available to all ODBC as well as integrated i5/OS® applications.
2.)Prepare the stored procedure call.
3.)Bind the parameters of the procedure, indicating whether each parameter is to be used for input to the procedure, output from the procedure, or input/output.
4.)Call the stored procedure.


Example:

CREATE PROCEDURE MYLIB.MYPROC (INOUT PARM1 CHAR(10)) EXTERNAL NAME MYLIB.MYPGM LANGUAGE COBOL GENERAL

In the above example, the procedure name is MYLIB.MYPROC.

This procedure references the COBOL program MYLIB.MYPGM. The program takes one input parameter called PARM1 which is a char field of length 10. This procedure does not return a result set.

STORED PROCEDURE : Very IMP For SQL Lovers

Defination

A stored procedure is a set of SQL commands that has been compiled and stored on the database server.

Once the stored procedure has been "stored", client applications can execute the stored procedure over and over again without sending it to the database server again and without compiling it again.

Stored procedures improve performance by reducing network traffic and CPU load

Some Details

Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Large or complex processing that might require the execution of several SQL statements is moved into stored procedures and all applications call the procedures only.

Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement

CALL procedure(…)

or

EXECUTE procedure(…)

Stored procedures can return result sets, i.e. the results of a SELECT statement. Such result sets can be processed using cursors by other stored procedures by associating a result set locator, or by applications. Stored procedures may also contain declared variables for processing data and cursors that allow it to loop through multiple rows in a table. The standard Structured Query Language provides IF, WHILE, LOOP, REPEAT, CASE statements, and more. Stored procedures can receive variables, return results or modify variables and return them, depending on how and where the variable is declared

Difference between Stored Procedures and Functions

a. Only function can Return a value (to be precise using the Return keyword)

b. Procedures can use ‘Return’ keyword but without any value being passed

c. Functions could be used in select statements, provided they don’t do any data manipulation inside and also should not have any OUT, IN OUT parameters

Comparison with Dynamic SQL

Because stored procedure statements are stored directly in the database, this can remove the compilation overhead that is typically required in situations where software applications send inline (dynamic) SQL queries to a database. However, most database systems implement statement caches to avoid repetitive compilation of dynamic SQL statements. In addition, pre-compiled SQL statements, while avoiding some overhead, add to the complexity of creating an optimal execution plan because not all arguments of the SQL statement are supplied at compile time. Depending on the specific database implementation and configuration, mixed performance results will be seen from stored procedures versus generic queries or user defined functions.

Programming Tip: HIDING LINES WHILE EDITING ** Especially for those Who work in companies where Implementation is carried out Mostly.

When you are working with source members that have many source lines, it is useful to exclude certain lines from the displayed member, allowing you to see lines from different areas of the source on the screen at the same time. Place X (Exclude) on any line you want to exclude from the display. This doesn't delete the line from the source member; it simply causes the excluded line(s) to be temporarily removed from the SEU display.
You can also use the X command in block format (XX). The example below shows how to exclude lines 3 through 6 (I entered a blank after the XX command to make it more readable):

*************** Beginning of data **************************
0001.00 111111111111111
0002.00 222222222222222
XX 3.00 333333333333333
0004.00 444444444444444
0005.00 555555555555555
XX 6.00 666666666666666
0007.00 777777777777777
****************** End of data *****************************

The result below shows that lines 3 through 6 have been hidden from view:

*************** Beginning of data **************************
0001.00 111111111111111
0002.00 222222222222222
- - - - ------------- 4 data records excluded ----------------------
0007.00 777777777777777
****************** End of data *****************************

To redisplay the lines, press F5 (Refresh) key, or use the SF or SL commands. SF (Show first) command shows the first excluded line, and SL (Show last) shows the last excluded line. You can also use SFn or SLn to show the first n lines or the last n lines of the excluded group of lines. The example below shows how to use the SF2 command to show the first two excluded lines:

*************** Beginning of data **************************
0001.00 111111111111111
0002.00 222222222222222
SF2 - - ------------- 4 data records excluded ----------------------
0007.00 777777777777777
****************** End of data *****************************

Notice below that the display shows the first two excluded lines and the text has changed to "2 data records excluded":

*************** Beginning of data **************************
0001.00 111111111111111
0002.00 222222222222222
0003.00 333333333333333
0004.00 444444444444444
- - - - ------------- 2 data records excluded ----------------------
0007.00 777777777777777
****************** End of data *****************************

DETERMINING WHETHER A JOB IS RUNNING INTERACTIVE OR BATCH IN CL

Use RTVJOBA command to find it out . Below is the small Program i made in order to find it out .
If anybody have some other technique also then he can kindly mail me .
PGM
DCL VAR(&JOBTYPE) TYPE(*CHAR) LEN(1)
RTVJOBA TYPE(&JOBTYPE)
IF COND(&JOBTYPE *EQ '0') THEN(GOTO START)
/* Interactive Program Section */
SBMJOB CMD(CALL PGM(ITSELFCL)) JOB(SBMITSELF)
SNDPGMMSG MSG('Tell user program Itself Submitted')
RETURN
START:
/* Mainline Batch Program */
If the Job Type is 0, it's running in batch, and if it's 1, it's interactive.

PRINTING A LIST OF ALL IBM SOFTWARE INSTALLED ON AS400 SERVER

You probably already know that if you enter GO LICPGM and select option 10, you can display a list of IBM software packages installed on your system. Unfortunately, this method might not display all IBM software packages installed, and it also doesn't let you print this list. You could, of course, simply use the Print Screen key, but you might have to print 15 different screens with messy formats, and you could inadvertently truncate data or forget to print a screen. There's a better solution. The following DSPSFWRSC (Display Software Resources) command

DSPSFWRSC OUTPUT(*PRINT)

not only prints out all your software package names, but it also prints the library name in which each package is located.

FINDING DEFAULT IBM PASSWORDS ON I- SERIES

Q. What command or utility checks for IBM-supplied user IDs that haven't had their passwords changed from their default values?

A. The command is ANZDFTPWD. You can either enter the command or enter GO SECTOOLS and select menu option 1. When run, ANZDFTPWD prints a report named QPSECPWD that shows all the user profiles on the system with default passwords. ANZDFTPWD optionally lets you disable or expire the password for user profiles with default passwords.

MANAGING DISK USE

To help you manage disk use on your AS/400, OS/400 includes the RTVDSKINF (Retrieve Disk Information) and PRTDSKINF (Print Disk Information) commands. You can use these two commands to monitor the utilization of your disks, or you can write your own queries over the data captured by RTVDSKINF to analyze changes in disk use.

You use command RTVDSKINF to gather information about all the objects on your system. The command outputs the information to member QCURRENT in file QAEZDISK in library QUSRSYS. For a large system with many thousands of objects, running RTVDSKINF can take many hours, so IBM requires you to run the command in batch. The easiest way to do this is to use the SBMJOB (Submit Job) command:

SBMJOB CMD(RTVDSKINF)
You use command PRTDSKINF to print the information that command RTVDSKINF generates in file QAEZDISK. PRTDSKINF lets you print a summary of your system information or print details of disk use by library, folder, owner, or object. For example, to print a list of libraries that are more than 1,000 K in size in descending order by size, you run the command

PRTDSKINF RPTTYPE(*LIB) +
OBJ(*NONE) +
MINSIZE(1000) +
SORT(*SIZE)

The online help for PRTDSKINF explains all the available options.
If the standard PRTDSKINF report options don't meet your needs, you can write your own queries over file QAEZDISK. In addition, you can save the data in member QCURRENT in QAEZDISK to a file and write queries to compare the output from different runs of RTVDSKINF.