IBM Data Studio Usage
Blogs

IBM Data Studio Usage for SQL Stored Procedures and Calling Stored Procedures in the RPGLE Program

By PIO TEAM on February 10, 2024

Why IBM Data Studio SQL Stored procedure is used

The SQL-stored procedures in Data Studio are Basic SQL statements written to minimize the amount of RPG and RPGLE code that is required. Result sets can be created that can be accessed directly by other languages, like .NET, and Java, instead of using values from the Database. In this way, IBM i Server and other servers can be used and connected using different front-end languages.

It is believed that SQL Stored Procedures created by IBM Data Studio will have their source in Data Studio. Nevertheless, after connecting to the IBM i Server, the Stored Procedure object will reside in the IBM i Server, like any other Program object. In this case, the object type will be PGM, and the attribute type will be CLE. The SQL Stored procedure will be stored in the system table QSYS2/SYSPROCS. 

IBM Data studio connection with IBM iSeries server

Follow the instructions in the below link to download IBM Data Studio software. (If required, ask your IT department for permission).

Once downloaded, it can be installed and set up on your computer. The Task launcher page will be opened by Open Data Studio after the connection has been established. The workspace location can be chosen once you select the workspace.

Then you will need to right-click on the database connection in Data Source Explorer and select for the new connection.

Here, select DB2 for i, and then in connection details put the IP address of the server with which you would like to connect. Put the user’s name ID and password in general information, in the options tab you can put values as property = naming and Value = system and add.

Second time, you can put property = libraries and Values = *LIBL.

Then you can evaluate the connection, once it is successful, you can establish the connection of Data Studio with the IBM i Server.

How to Write a SQL Stored Procedure code in IBM Data Studio

To create a SQL stored procedure, you first need to select a project, so select the Database project, then select the stored procedure option.

Once selected, a default page will appear where you can write code. The entry parameters of a stored procedure can be decimal, character, Varchar, and many others. There are two types of parameters: IN and INOUT.

IN means only incoming parameters into the Stored Procedure. INOUT means the parameter can be incoming value into Stored Procedure and can send value out to calling program or routine.

While writing the stored procedure you will need to define the number of result sets that can be returned in table format. Various options can be taken like header specification in RPGLE programs.

Comments can be written with — sign.

Below is an example of a Stored Procedure
 
Create Procedure SP001S (INOUT pErroID  Char(10),
                          IN sIncomingParam1 Dec(5,0),
                    IN sIncomingParam2 Char(10))
 
   Result Sets 1
       Language SQL
       Set Option Commit = *None, DBGView = *Source, Output =
        *PRINT, SQLPATH = *LIBL, TGTRLS = V7R3M0
   -- **********************************************************
   -- Start of Stored Procedure Declaration
   --***********************************************************
        P1: Begin
 
   --**********************************************************
   -- Variable Declaration
   --**********************************************************
      Declare Variable1 Char(10);
        Declare Variable2 Char(10);
 
   --*********************************************************
   -- All Cursor Declaration
   --*********************************************************
   Declare DemoCursor Cursor With Return For
   Select
   Field1 As FieldRenamed1,
   Field2 As FieldRenamed2,
   Trim(Field3) As FieldRenamed3,
   From File1
   Left Join File2 On Field11 = Field1 And Field12 = Field2
   Where Field3 = sIncomingParam2 And Field4 >  sIncomingParam1;
 
--*******************************************************
-- Main Processing Control Procedure
--*******************************************************
 
MAIN: Begin
       Set Variabl1 =  ‘PGM1’;
       Set Variable2 = ‘ ‘;
       Set pErroID = ‘ ‘;
--*************************************************
-- Validations Of Incoming Parameter Values
--*************************************************
If sIncomingParam1 = 0 Then
    Set pErroID = ‘ER1’;
    Leave Main;
EndIf;
If sIncomingParam2 = ‘ ‘ Then
    Set pErroID = ‘ER2’;
    Leave Main;
EndIf;
 
--**************************************************
-- Open Cursors To Return Result Sets
--**************************************************
Curset: Begin
           Open DemoCursor;
           Set Result Sets Cursor DemoCursor;
 End Curset;
 End Main;
 --Call Error Procedure and write error
 If pErrorID <> ‘ ‘ Then
    CALL ERRORPGM (pErrorID,’Error’);
 EndIf;
End;   --Here Result set ends

Object Creation and where to find the details of the Stored Procedure

IBM Data Studio has the option to compile the code once it has been written. Using which object creation can be done? The library where the object will reside on the AS400 Server should be inserted during compilation along with system default libraries.

WRKOBJ command can be used to verify if the object has been created once the object has been compiled on AS400.

The details can also be found in system table QSYS2/SYSPROCS, the stored procedure details with several parameters, whether IN or INOUT all details will be saved also the Schema or Library where the Object of the Stored procedure is currently residing will be present in the table.

Usage of the Stored Procedure

The stored procedure can be accessed by other servers, such as Microsoft servers or other servers that can pass the parameter directly, e.g., in .NET or Java.

In this case, the data will be returned in the result set table format instead of a block of data or parameter values, saving processing time on the front-end languages and allowing the IBM i/AS400 server to filter it based on the business logic to be passed.

This Stored Procedure can be accessed by RPGLE/SQLRPGLE programs within the IBM i/AS400 server as well if required.

The main keywords required to access the stored procedure are in D specification and need to declare SQL type as below

D  ResultSet1                   s                      sqltype(result_set_locator) varying

D  ResultSet2                   s                      sqltype(result_set_locator) varying

The C specification or execution of code needs to be written as below example

Exec SQL

          Associate  Result Set Locators

          (:ResultSet1, :ResultSet2)

          With Procedure Procedure_Name;

Exec SQL

             Allocate Cursor1   Cursor

             For  Result Set: ResultSet1;

For fetching the next records, it is the same

Exec SQL

           Fetch Next From Cursor1 in :Var1;

After which the looping logic and other logic can be written.

Conclusion

IBM Data Studio acts as a catalyst for IBM i/AS400 developers seeking an efficient and integrated solution for SQL stored procedure development. By seamlessly integrating these procedures into RPGLE programs, organizations can enhance data management, streamline workflows, and achieve greater overall efficiency in their database applications. Data Studio’s GIT integration allows all sources to be saved and managed. Embrace the power of IBM Data Studio and RPGLE programming to unlock a new level of productivity in your data-centric projects. Additionally, we can use the tool to create functions and SQL files along with stored procedures. Other servers besides the IBM i server can also be connected to the tool.

SHARE: