AUG:SQL Procedure Programs
SQL Procedure Programs
Special activities are required to ensure that native IBM-i (service)programs can be used from within SQL as an external procedure. Using TD/OMS Action definitions are a perfect way to make sure, that the IBM SQL schema contains the correct information, after a transfer of program objects, used as SQL external procedures. The initial process of making programs known within the IBM SQL schema is not part of the actions described.
When this configuration for SQL external procedures have been made, your TD/OMS fix transfer engine will process the common object creation steps for programs not found in the IBM SQL schema. Programs that are indeed found in the IBM SQL schema, will be transfered by means of a save\restore and that process will update the IBM SQL schema. The programs required for these actions definitions, are available in the TD/OMS Kernel as of V6.0 M03.
What the following Actions do is:
- Checking program name, library in the external name of procedures, and the external language in the IBM SQL schema at start of the object transfer
- Process Save/restore procedure or standard object creation, based on result of the checking at the start
- Follow the same checking and processing during pre-distribution phase of a transfer, in case a *(SRV)PGM object should be transferred to the distribution library.
- Removing original SQL schema entry, after program defined as external procedure, has been moved into other DB collection (library)
This means that the TD/OMS transfer does all the work to keep the IBM SQL schema synchronous with the new location of native IBM-i programs.
Entering the required Actions
TD/OMS provides various standard Action procedures, which take care of the IBM SQL schema synchronization. You have to create those actions embedded in Action Groups, to make all functions available to the TD/OMS transfer process. Enter the command STRACM, press F6 and enter the Action exactly as it appears in this example.
OMC001 Action Maintenance *DATE
OMC00102 Maintain Action *TIME
CHANGE
Action .................: SQL check & drop external routine
Action command .........: STRPRCOMS
Action cluster..........: SQLSCHEMA F4=List
Bottom
F3=Exit F5=Refresh F8=Selections F10=Details F12=Cancel
Enter the description of the SQLSCHEMA cluster if you are asked to do so. Then press F10=Details, enter SQLRTNDRP for the program name and *V5R1M0 for the OMS interface level .
Start action procedure (STRPRCOMS)
Type choices, press Enter.
Program . . . . . . . . . . . . SQLRTNDRP Name
Library . . . . . . . . . . . *LIBL Name, *LIBL
OMS Interface level . . . . . . *V5R1M0 *V3R0M0, *V5R1M0
BOTTOM
F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use this display
F24=More Keys
The Action you have created by doing this is:
Action Command Cluster SQL check & drop external routine STRPRCOMS APGM(SQLRTNDRP) INTL(*V5R1M0) SQLSCHEMA
Now enter also these actions.
Action Command Cluster SQL check external object in schema STRPRCOMS APGM(SQLRTNCHK) INTL(*V5R1M0) SQLSCHEMA SQL external object transfer STRPRCOMS APGM(SQLRTNTFR) INTL(*V5R1M0) SQLSCHEMA Action/ UEP replacing default transfer STRPRCOMS APGM(DFTCRTUEP) INTL(*V5R1M0) SQLSCHEMA Duplicating in UEP, replacing def. transfer CRTDUPOBJ OBJ(&OBJC) FROMLIB(&FROL) OBJTYPE(&OBJT) TOLIB(&TOOL) SQLSCHEMA
After you have created the action definitions, the Work with Actions list should look something like this (use the Action Cluster field to only select SQLSCHEMA actions):
OMC001 Action Maintenance OMC00101
Work with Actions
Position to ............: Action cluster .........: SQLSCHEMA
2=Change 3=Copy 4=Delete 5=Display 8=Selections 12=Details
Opt Command Description Cluster
STRPRCOMS SQL external object transfer SQLSCHEMA
STRPRCOMS SQL check external object in schema SQLSCHEMA
STRPRCOMS SQL check & drop external routine SQLSCHEMA
STRPRCOMS Action/ UEP replacing default transfer SQLSCHEMA
CRTDUPOBJ Duplicating in UEP, replacing def. transfer SQLSCHEMA
Bottom
F3=Exit F5=Refresh F6=Add F12=Cancel
Creating the Action Groups
After you have entered the Actions, you are able to place them into an Action Group. Although it is strictly not required to embed actions in Action Groups, it is preferred to do so. If you put related Actions into the same Group, then you have simple overview, but also you can add error-control parameters, that determines the process flow of the Actions you have defined.
The basic set-up requires three Action Groups. One group to hold the Actions processing the common transfer; another group to hold the local processing Actions in case the transfer includes a distribution part, and a third group to take care of the schema update or cleanup at the end of the transfer.
Enter the command STRAGM, press F6=Add and enter these groups (marked with ">").
OMC004 Action Group Maintenance OMC00401
Work with Action Groups
Position to ............:
2=Change 4=Delete 5=Display 8=Selections 12=Details
Opt Action Group Sng
> SQL check & drop from schema
> SQL distribution procedure
> SQL object transfer procedure
Bottom
F3=Exit F5=Refresh F6=Add F12=Cancel
Action Group Details
Use option "12=Details" on the work with Action groups screen to load the details.
Place the "SQL check & drop external routine" action into the "SQL check & drop from schema" group.
Note the values of the error control indicators: On error=0, Critical=1 (yes) and Propagate error=1 (yes). After you have created the action details the Work with Action Group details should look something like this:
OMC005 Action Group Detail Maintenance
OMC00501 Work with Action Groups Details
Action group: SQL check & drop from schema
2=Change 4=Delete 5=Display 8=Selections 12=Details
Opt Seq # Sel Typ Err Cri Pro Description
10 0 A 0 1 1 SQL check & drop external routine
Bottom
F3=Exit F5=Refresh F6=Add F12=Cancel
SQL check & drop from schema Action Group Selections
In the next step of Action group definition, you must connect selections to the group.
Use option 8=Selections and add the following selection entries:
OMC011 Action Selection Maintenance
OMC01101 Maintain Action Selection
DISPLAY
Action / action group ..: G SQL check & drop from schema
Processing step ........: EP Suppress subsequent ....: 0
When to activate .......: 0 Where to activate ......: 2
Object code ............: *ALL Name, name*, *LIST, *ALL
Object type ............: *LIST + Object type, *LIST, *ALL
Object attribute .......: *ALL Object attribute, *LIST, *ALL
Object library .........: *ALL Library name, *LIST, *ALL
Object detail ..........: *ALL Detail code, *BLANK, *LIST, *ALL
Detail attribute .......: *ALL Detail attribute, *LIST, *ALL
Object class ...........: * Class code, *
Object version .........: * Number, *
Application code .......: *ALL Application code, *LIST, *ALL
Environment code .......: *ALL Environment code, *LIST, *ALL
Fix number .............: *ALL Active Fix code, *LIST, *ALL
Solution type ..........: 1 + 1=*CHANGE, 9=*TERMINATE, *
Processing direction ...: * 0=Up 1=Down 2=Horizon *=Always
F3=Exit F5=Refresh F9=Addresses F12=Cancel
Position the cursor on the *LIST value of the Object type and press F14 to enter the object types involved (*PGM and *SRVPGM) on the Specify *LIST value details screen.
OMC016 Action Selection Maintenance
OMC01601 Specify *LIST value details
*LIST detail type .: Object type Position to
1=Add 4=Remove
Opt *LIST detail
*PGM
*SRVPGM
F3=Exit F5=Refresh F12=Cancel
Return to the Maintain Action Selection screen and press F9 to enter any remote location address for this selection. Press F10 on the Specify Addresses screen to make the Action globally for all remote locations. This has the advantage that the Action group will be automatically active for all Remote Locations that are added after definition of this selection.
This action is activated during the object transfer process on the sending system and on the receiving system.
SQL distribution procedure Action Group Details
Use option 12=Details to load the Action group details.
Place the Actions defined before as "SQL check external object in schema", "SQL external object transfer" and the "Action/ UEP default transfer override" into the Action group "SQL distribution procedure".
Note the values of the error control indicators: On error gets values 0, 1, & 2 respectively, Critical flag for all is 1(yes), and Propagate error value is 0(no) for all details. After you have defined these actions as group details the Work with Action Group details should look something like this:
OMC005 Action Group Detail Maintenance
OMC00501 Work with Action Groups Details
Action group: SQL distribution procedure
2=Change 4=Delete 5=Display 8=Selections 12=Details
Opt Seq # Sel Typ Err Cri Pro Description
10 0 A 0 1 0 SQL check external object in schema
20 0 A 1 1 0 SQL external object transfer
30 0 A 2 1 0 Action/ UEP replacing default transfer
Bottom
F3=Exit F5=Refresh F6=Add F12=Cancel
The first action detail will execute the check for external procedure on the SQL schema. In case of transferring a object being NOT a SQL external procedure, the group detail generates an error. Based on this error the second group detail (value 1 = not-if-error) or the third group detail (value 2 = if-error) will be processed. The occurrence of the error is only relevant within this action group. So the propagate error flag is set to 0 for all.
SQL distribution procedure Selections
After this part of the definition, you must connect selections to the Action group.
Use option 8=Selections and enter the following selection;
OMC011 Action Selection Maintenance
OMC01101 Maintain Action Selection
DISPLAY
Action / action group ..: G SQL distribution procedure
Processing step ........: CO Suppress subsequent ....: 0
When to activate .......: 1 Where to activate ......: 0
Object code ............: *ALL Name, name*, *LIST, *ALL
Object type ............: *LIST + Object type, *LIST, *ALL
Object attribute .......: *ALL Object attribute, *LIST, *ALL
Object library .........: *ALL Library name, *LIST, *ALL
Object detail ..........: *ALL Detail code, *BLANK, *LIST, *ALL
Detail attribute .......: *ALL Detail attribute, *LIST, *ALL
Object class ...........: * Class code, *
Object version .........: * Number, *
Application code .......: *ALL Application code, *LIST, *ALL
Environment code .......: *ALL Environment code, *LIST, *ALL
Fix number .............: *ALL Active Fix code, *LIST, *ALL
Solution type ..........: 1 + 1=*CHANGE, 9=*TERMINATE, *
Processing direction ...: * 0=Up 1=Down 2=Horizon *=Always
F3=Exit F5=Refresh F9=Addresses F12=Cancel
Position the cursor on the *LIST value of the Object type and press F14 to enter the object types involved (*PGM and *SRVPGM) on the Specify *LIST value details screen.
OMC016 Action Selection Maintenance
OMC01601 Specify *LIST value details
*LIST detail type .: Object type Position to
1=Add 4=Remove
Opt *LIST detail
*PGM
*SRVPGM
F3=Exit F5=Refresh F12=Cancel
This Action group will only run on the sending system at the start of the distribution part of the transfer process.
SQL object transfer procedure Action Group Details
Use option 12=Details to load the details.
Place the Actions defined before as "SQL check external object in schema", "SQL external object transfer" and "Duplicating in UEP, overriding def. transfer" into the Action group definition "SQL object transfer procedure".
Note the values of the error control indicators: On error gets values 0, 1, & 2 respectively, Critical flag for all is 1(yes), and Propagate error value is 0(no) for all details. After you have created all action details the Work with Action Group details should look something like this:
OMC005 Action Group Detail Maintenance
OMC00501 Work with Action Groups Details
Action group: SQL object transfer procedure
2=Change 4=Delete 5=Display 8=Selections 12=Details
Opt Seq # Sel Typ Err Cri Pro Description
10 0 A 0 1 0 SQL check external object in schema
20 0 A 1 1 0 SQL external object transfer
30 0 A 2 1 0 Duplicating in UEP, replacing def. transfer
Bottom
F3=Exit F5=Refresh F6=Add F12=Cancel
The first action detail execute the check for external procedure on the SQL schema. In case of transferring a object being NOT a SQL external procedure, the group detail generates an error. Based on this error the second group detail (value 1 = not-if-error) or the third group detail (value 2 = if-error) will be processed. The occurrence of the error is only relevant within this action group. So the propagate error flag is set to 0 for all.
SQL object transfer procedure Selections
Next you have to continue the group definition by connecting selections to the Action group.
Use option 8=Selections and add the following selection entries;
OMC011 Action Selection Maintenance
OMC01101 Maintain Action Selection
DISPLAY
Action / action group ..: G SQL object transfer procedure
Processing step ........: CO Suppress subsequent ....: 0
When to activate .......: 0 Where to activate ......: 2
Object code ............: *ALL Name, name*, *LIST, *ALL
Object type ............: *LIST + Object type, *LIST, *ALL
Object attribute .......: *ALL Object attribute, *LIST, *ALL
Object library .........: *ALL Library name, *LIST, *ALL
Object detail ..........: *ALL Detail code, *BLANK, *LIST, *ALL
Detail attribute .......: *ALL Detail attribute, *LIST, *ALL
Object class ...........: * Class code, *
Object version .........: * Number, *
Application code .......: *ALL Application code, *LIST, *ALL
Environment code .......: *ALL Environment code, *LIST, *ALL
Fix number .............: *ALL Active Fix code, *LIST, *ALL
Solution type ..........: 1 + 1=*CHANGE, 9=*TERMINATE, *
Processing direction ...: * 0=Up 1=Down 2=Horizon *=Always
F3=Exit F5=Refresh F9=Addresses F12=Cancel
Position the cursor on the *LIST value of the Object type and press F14 to enter the object types involved (*PGM and *SRVPGM) on the Specify *LIST value details screen.
OMC016 Action Selection Maintenance
OMC01601 Specify *LIST value details
*LIST detail type .: Object type Position to
1=Add 4=Remove
Opt *LIST detail
*PGM
*SRVPGM
F3=Exit F5=Refresh F12=Cancel
Return to the Maintain Action Selection screen and press F9 to enter any remote location address for this selection. Press F10 on the Specify Addresses screen to activate the Action for all remote locations. This has the advantage that the Actions is automatically selected for processing on remote systems that will be added after definition of this selection.
This Action group will be run on the sending system and the receiving system as part of the transfer process.