Customize your DB2 CLP prompt
You have a nice DB2 CLP prompt, looks like “db2>”, but you want more, you want a efficient prompt. It is possible with DB2 to add or modify your CLP ! DB2 have a registry variable named DB2_CLPPROMPT which allows us to define the prompt to be used in the CLP interactive mode.
To define this variable, let’s use the DB2set command:
Db2set DB2_CLPPROMPT="db2isgreat> "
Our new DB2 Prompt will be “db2isgreat> “. It’s much more better, but we can do more. DB2_CLPPROMPT registry variable can contain the tokens %n, %ia, %d, %da and %i:
- %n – New line
- %ia – Authorization ID of the current instance attachment
- %d – Local alias of the currently connected database
- %da – Authorization ID of the current database connection
- %i – Local alias of the currently attached instance
Now, let’s try with these tokens:
Db2set DB2_CLPPROMPT="%ia@%i, %da@%d> "
With an instance attachment to instance “DB2″ with authorization ID “mycado”. Database “sample” with authorization ID “mycadoax”, will return something like:
MYCADO@DB2, MYCADOAX@SAMPLE >
You can now everytime where you are, and one which instance/database you’re working on.
Let’s start with JCL
You probably know PHP, Java, C, and lot of other programming languages, but what about JCL ? No, it’s not about Java, it’s about Job Control Language a scripting language used on mainframe to instruct the system on how to run a batch job. It is possible to submit JCL for batch processing or directly by to start a JCL procedure (PROC). JCL is very important to create, check, correct and run the daily batch workload.
It’s easy, you have three basic statements:
- JOB: Provides a name (jobname) for the batch.
- EXEC: Provides the name of a program to execute.
- DD: For Data Definition, provides inputs/outputs to the program.
Let’s see a JCL example:
//MYJOB JOBTES 1 //MYSORT EXEC PGM=SORT //SORTIN DD DISP=SHR,DSN=SUP01.TAB.TEST //SORTOUT DD SYSOUT=* //SYSOUT DD SYSOUT=* //SYSIN DD * SORT FIELDS=(1,4,CH,A) /*
Now, try to understand what’s happen here.
- MYJOB is the jobname associates to the workload, here it’s “JOBTES”.
- MYSORT is the stepname, which ask the system to execute a program called “SORT”.
- SORTIN is the program input, here with the DSN (Data Set Name) SUP01.TAB.TEST, and the dataset can be shared (DISP=SHR).
- SORTOUT is the SORT program output.
- SYSOUT specifies to send system output to JES (Job Entrey Subsystem), but it’s also possible to send the outpu to a dataset.
- SYSIN tell the SORT program which fields of the SORTIN data records are to be sorted.
Enough for the moment, JCL is quite hard at beginning !
Queries on XML data with XQuery
We know how to store native XML data in our DB2 tables, and now we will see how we can access to these data. We can choose between standard SQL queries and XQuery.. or both ! The first solution, with a SQL query, only query at the column level of your table; this query will return the full XML data. The second solution, with XQuery, allow us to make a “query” inside our XML data.
For the SQL query, nothing more than a SELECT:
SELECT id, info from client
Easy, but not really powerful for XML data, let’s try with XQuery, who give us two functions for DB2. db2-fn:sqlquery and db2-fn:xmlcolumn. The first function retrieves a sequence that is the result of an SQL fullselect and the second retrieves a sequence from a column. One important thing you should keep in mind, SQL is not a case-sensitive language alors que XQuery is a case-sensitive language.
An example which return all the XML data from the “info” column:
XQUERY db2-fnxmlcolumn ('CLIENT.INFO')
Which is same as this SQL query:
SELECT info FROM client
Let’s see something more nice. This query will return all the elements in <name> , inside the “info” column, and with the <city> element which containt “Paris”:
XQUERY declare defaut element namespace "http://posample.org";
for $d in db2-fn:xmlcolumn('CLIENT.INFO')/clientinfo
where $d/addr/city="Paris"
return <out>{$d/name}</out>
db2-fn:xmlcolumn retrieves the data from the “info” column in the “client” table. We add a $d variable, for each element of <clientinfo>, and we use a where to filter the <city> element which should be “Paris. To finish, we use <out> to output the data:
<out xmlns="http://posample.org"> <name>Sophie Bool</name> </out>
To finish, the same example with a SQL query inside the XQuery:
XQUERY declare default element namespace "http://posample.org";
for $d in db2-fn:sqlquery('SELECT info FROM client')/clientinfo
where $d/addr/city="Paris
return <out>{$d/name}</out>
Enough for today, we will see next time how to do more complex and more powerful query with XQuery !
Nightshot from home, Beijing
In south of Sanlitun (南三里屯兒), view of the CBD area, CCTV tower, Twin WTC towers,..
南三里屯兒