Set path vs current schema

1. Set path vs current schema

2. Set Current Schema • ‘The CURRENT SCHEMA special register specifies a VARCHAR(128) value that identifies the schema name used to qualify unqualified database object references where applicable in dynamically prepared SQL statements. ‘ • Copied IBM’s SQL reference manual

3. We set the current schema. We created a User Defined field and then tried to use it in a create table statement. The create table failed because the statement couldn’t find the user defined type. Why?

4. Current Path • ‘The CURRENT PATH special register specifies the SQL path used to resolve unqualified distinct type names, function names, and procedure names in dynamically prepared SQL statements.’ • Copied from IBM’s SQL Reference Manual • Set path = ‘collection’

5. Function Foot Prints! Watch your parameter definitions!

6. CL Programming Review

7. CL Restrictions • Only open 5 files • Cannot update files • Only 25 levels of nested iteration and selection statements • Cannot create reports – only execute them

8. Begin and end • PGM • Also used to include parameters passed to the program • ENDPGM • Last statement executed and control returns to the calling program

9. Declaring Variables • Declaring files (DCLF) • Specify the file name • If more than one file used in the program than specify the opnid • Limit of 5 files open in the program • Declaring variables (DCL) • *DEC, *CHAR, *LGL, *INT, *UINT • Date, Time, and Timestamp variables are treated as *CHAR

10. Defining a Logical Block Do Enddo • Can be embedded in iteration, and selection statements

11. Iteration Dowhile (condition) Enddo • Tests the condition first. If the condition is true, then the logic block is executed.

12. Iteration Dountil (condition) Enddo • Tests the condition after the logic block is executed • In other words, the logic block is always executed at least once.

13. Iteration DOFOR &variable &from &to &by ENDO • Executes the loop by checking the contents of &variable. &variable starts the the &from value. The loop ends when &variable reaches the &to value. &variable is incremented by the &by variable. If &by is left out then &variable is incremented by 1.

14. Selection If (condition) Else End-if. • Allows execution of code based on a condition. If the condition is false, then the else logic is executed. • If statements process only one CL command if true and one CL command if false • Often used in combination with the DO ENDDO logic block.

15. Selection Select When cond(condition 1) then(cl command) When cond(condition 2) then(cl command) Otherwise (cl command) Endselect • Executes the first true condition and then exits the structure

16. Working with Files • RCVF OPNID(openidname) • Reads records from a file • Works with database files and display files • SNDF OPNID(openidname) • Writes to a file • Works only with display files • SNDRCVF OPNID(openidname) • Reads a record from a file • Writes to a file • Works only with display files • OPNID only needed if more than one file is opened in the program

17. MONitoring for MeSsaGes • MONMSG MSGID(CPF9999) EXEC(cl command) • Used to trap error messages • Used to avoid the Black Screen of Death

18. Indicators • Used in display file to pass information between the program and the display file. • Two types of indicators • Response (ie function keys) • Option (ie reverse image)

19. Happy Birthday Program Write an interactive program that prompts for a userid and sends a happy birthday greeting to that user. Program should terminate when F3 is pressed

20. CL commands for the lab • RUNSQLSTM • DSPOBJD • DSPFD • DSPDBR • DSPOBJAUT • RTVJOBA • DSPSPLF