IBM Systems Magazine, Mainframe - September/October 2010 - 52

Developer
Open a cursor CUSTOMERS_CS and read all of the customer data stored in table DSN8910.CUSTOMERS. Store the customer data as an XML document into table DSN8910.CUSTOMER with the document being stored in the INFO column. The first thing I usually do is prototype the SQL I need for a program before I write any code. The SQL can be written and tested using the DB2 interactive tool SPUFI on z/OS or you can take advantage of IBM’s free Data Studio (www.ibm.com/developerworks/ downloads/im/data/). If you’re using SPUFI to change the default settings for output-record length, the MAX CHAR FIELD controls the maximum length of a single column. I forgot about this setting and my XML column was truncated to 80 characters. I changed my record length to 4,000 and the max column length to 4,000 and had no problems with the sample data. In this sample program I’ve put the SQL to select all of the customers into the CUSTOMERS_CS cursor. A good practice is to fetch the data from the cursor into a working storage area defined using the DCLGEN instead of user-defined variables. I’ve found people make mistakes or the table structure changes and these changes aren’t picked up and reflected when they recompile the program. To help reduce the chance of having an incorrect host variable defined, I use the IBM DB2 software-generated SQL and source-language declarations, also known as DCLGEN. The DCLGEN utility will produce the correct host-variable definition based on the definition in the DB2 catalog. I also consider the generated host variables to ensure I’m not going to have any conversion problems when moving the data. As I started this project, the first thing I noticed in the DCLGEN for these two tables is customer IDs stored on CUSTOMERS is an INTEGER field and on the CUSTOMER table it is defined as BIGINT in the cid column. An INTEGER is defined in COBOL as PIC S9(9) and BIGINT is defined as PIC S9(18). Since we’re going from customers to customer we don’t have to worry about data truncation. I decided to code my SQL such that the integer to bigint conversion will occur during the SELECT process, and not during the INSERT process, by using the BIGINT cast function. I also noticed the DCLGEN definition for a CLOB column is 1M. If you’re going to store large documents, you may need to increase this up to the maximum size of 2G. Now DCLGEN is set up, you must define the cursor. I usually put my DECLARE CURSOR definitions after the INCLUDE DCLGEN statements. The following is the CUSTOMER_CS cursor declaration using XML publishing functions to format the relational data into an XML document. The program will open this cursor then perform an insert for each row fetched from the CUSTOMER_CS cursor based on the CUSTOMER table. You should always code and test your SQL before you put it in the program. To simulate performing the insert to verify the XML document is valid, I added an INSERT statement in front of the SELECT statement. When I ran this in SPUFI, I was surprised to see an SQL error. The INSERT failed with “-20345, ERROR: THE XML VALUE IS NOT A WELL-FORMED DOCUMENT WITH A SINGLE ROOT ELEMENT”. Being new to XML, I had no idea what to do to fix this. After doing some research I found I needed to incorporate the XMLDOCUMENT function into this statement. You can view the CUSTOMER_CS cursor in Code Sample 1 (page 51) and the resulting XML document in Code Sample 2 (above). When using XML publishing functions you may have to do some trial and error with nesting of functions to get the correct results. I’ve added some line numbers to both the cursor as well as the output for reference. The basic components used to define an XML document are: a document name, a namespace to uniquely identify elements in the document and an attribute to identify the document. An example of this can be seen in line 42. The XMLELEMENT function as seen on line 4 is used
41)  42)  43)  44) 45) 46) 47) 48) 49) 50) 51)  JOHN DOE  4356 SARATOGA AVE SAN JOSE CALIFORNIA 94534-0567

Code Sample 2

52)  53) 1 54) 1 55) U 56) M 57) 1 58) 

52

SEPTEMBER /OCTOBER 2010

ibmsystemsmag.com/mainframe


http://www.ibm.com/developerworks/downloads/im/data/ http://www.ibm.com/developerworks/downloads/im/data/ http://www.ibmsystemsmag.com/mainstream

IBM Systems Magazine, Mainframe - September/October 2010

Table of Contents for the Digital Edition of IBM Systems Magazine, Mainframe - September/October 2010

IBM Systems Magazine, Mainframe - September/October 2010
Contents
Editor's Desk:  Not So Secret Sauce
Dashboard:  Find an Extra Day
Think Smarter:  Competing Takes Equal Parts Growth and Restraint
Data Display:  Privacy:  Who Do You Trust?
Insider:  Untangling the Web of Processes and Technology
Trends:  System z as the Hub of a Workload-Optimized, Business Analytics Systems
Case Study: A Merger Made Easy" EmblemHealth Saves Money By Easing into an In-Sourced Computing Model
Expanding Beyond Borders:  IBM zEnterprise Systems Announcement Delivers a New Dimension in Computing.
Reducing Complexity:  The Next-Generation System z Server Is More Than Just a Fast, Scalable Solution
Focus on Storage:  IBM Tivoli Key Lifecycle Manager Solves Security Problems and Meets New Standards
Administrator:  z/OS Management Facility V1.12 Includes New Workload-Management and Resource-Monitoring Functionality
Developer: pureXML Extends Availability and Scalability to DB2 for z/OS
Solutions
Advertisers' Index
Stop Run:  IBM Cooling Expert Roger Schmidt Says the Positioning of Machines Matters
2011 Mainframe Buyer's Guide
IBM Systems Magazine, Mainframe - September/October 2010 - IBM Systems Magazine, Mainframe - September/October 2010
IBM Systems Magazine, Mainframe - September/October 2010 - Cover2
IBM Systems Magazine, Mainframe - September/October 2010 - 1
IBM Systems Magazine, Mainframe - September/October 2010 - Contents
IBM Systems Magazine, Mainframe - September/October 2010 - 3
IBM Systems Magazine, Mainframe - September/October 2010 - 4
IBM Systems Magazine, Mainframe - September/October 2010 - 5
IBM Systems Magazine, Mainframe - September/October 2010 - 6
IBM Systems Magazine, Mainframe - September/October 2010 - 7
IBM Systems Magazine, Mainframe - September/October 2010 - 8
IBM Systems Magazine, Mainframe - September/October 2010 - 9
IBM Systems Magazine, Mainframe - September/October 2010 - Editor's Desk:  Not So Secret Sauce
IBM Systems Magazine, Mainframe - September/October 2010 - 11
IBM Systems Magazine, Mainframe - September/October 2010 - Dashboard:  Find an Extra Day
IBM Systems Magazine, Mainframe - September/October 2010 - 13
IBM Systems Magazine, Mainframe - September/October 2010 - 14
IBM Systems Magazine, Mainframe - September/October 2010 - 15
IBM Systems Magazine, Mainframe - September/October 2010 - Think Smarter:  Competing Takes Equal Parts Growth and Restraint
IBM Systems Magazine, Mainframe - September/October 2010 - 17
IBM Systems Magazine, Mainframe - September/October 2010 - 18
IBM Systems Magazine, Mainframe - September/October 2010 - 19
IBM Systems Magazine, Mainframe - September/October 2010 - Data Display:  Privacy:  Who Do You Trust?
IBM Systems Magazine, Mainframe - September/October 2010 - 21
IBM Systems Magazine, Mainframe - September/October 2010 - Insider:  Untangling the Web of Processes and Technology
IBM Systems Magazine, Mainframe - September/October 2010 - 22A
IBM Systems Magazine, Mainframe - September/October 2010 - 22B
IBM Systems Magazine, Mainframe - September/October 2010 - 23
IBM Systems Magazine, Mainframe - September/October 2010 - 24
IBM Systems Magazine, Mainframe - September/October 2010 - 25
IBM Systems Magazine, Mainframe - September/October 2010 - Trends:  System z as the Hub of a Workload-Optimized, Business Analytics Systems
IBM Systems Magazine, Mainframe - September/October 2010 - 27
IBM Systems Magazine, Mainframe - September/October 2010 - 28
IBM Systems Magazine, Mainframe - September/October 2010 - 29
IBM Systems Magazine, Mainframe - September/October 2010 - Case Study: A Merger Made Easy" EmblemHealth Saves Money By Easing into an In-Sourced Computing Model
IBM Systems Magazine, Mainframe - September/October 2010 - 31
IBM Systems Magazine, Mainframe - September/October 2010 - 32
IBM Systems Magazine, Mainframe - September/October 2010 - 33
IBM Systems Magazine, Mainframe - September/October 2010 - Expanding Beyond Borders:  IBM zEnterprise Systems Announcement Delivers a New Dimension in Computing.
IBM Systems Magazine, Mainframe - September/October 2010 - 35
IBM Systems Magazine, Mainframe - September/October 2010 - 36
IBM Systems Magazine, Mainframe - September/October 2010 - 37
IBM Systems Magazine, Mainframe - September/October 2010 - Reducing Complexity:  The Next-Generation System z Server Is More Than Just a Fast, Scalable Solution
IBM Systems Magazine, Mainframe - September/October 2010 - 39
IBM Systems Magazine, Mainframe - September/October 2010 - 40
IBM Systems Magazine, Mainframe - September/October 2010 - 41
IBM Systems Magazine, Mainframe - September/October 2010 - Focus on Storage:  IBM Tivoli Key Lifecycle Manager Solves Security Problems and Meets New Standards
IBM Systems Magazine, Mainframe - September/October 2010 - 43
IBM Systems Magazine, Mainframe - September/October 2010 - 44
IBM Systems Magazine, Mainframe - September/October 2010 - 45
IBM Systems Magazine, Mainframe - September/October 2010 - Administrator:  z/OS Management Facility V1.12 Includes New Workload-Management and Resource-Monitoring Functionality
IBM Systems Magazine, Mainframe - September/October 2010 - 47
IBM Systems Magazine, Mainframe - September/October 2010 - 48
IBM Systems Magazine, Mainframe - September/October 2010 - 49
IBM Systems Magazine, Mainframe - September/October 2010 - Developer: pureXML Extends Availability and Scalability to DB2 for z/OS
IBM Systems Magazine, Mainframe - September/October 2010 - 51
IBM Systems Magazine, Mainframe - September/October 2010 - 52
IBM Systems Magazine, Mainframe - September/October 2010 - 53
IBM Systems Magazine, Mainframe - September/October 2010 - Solutions
IBM Systems Magazine, Mainframe - September/October 2010 - Advertisers' Index
IBM Systems Magazine, Mainframe - September/October 2010 - Stop Run:  IBM Cooling Expert Roger Schmidt Says the Positioning of Machines Matters
IBM Systems Magazine, Mainframe - September/October 2010 - Cover3
IBM Systems Magazine, Mainframe - September/October 2010 - Cover4
IBM Systems Magazine, Mainframe - September/October 2010 - 2011 Mainframe Buyer's Guide
IBM Systems Magazine, Mainframe - September/October 2010 - BG-2
IBM Systems Magazine, Mainframe - September/October 2010 - BG-3
IBM Systems Magazine, Mainframe - September/October 2010 - BG-4
IBM Systems Magazine, Mainframe - September/October 2010 - BG-5
IBM Systems Magazine, Mainframe - September/October 2010 - FBG-6
IBM Systems Magazine, Mainframe - September/October 2010 - BG-7
IBM Systems Magazine, Mainframe - September/October 2010 - BG-8
IBM Systems Magazine, Mainframe - September/October 2010 - BG-9
IBM Systems Magazine, Mainframe - September/October 2010 - BG-10
IBM Systems Magazine, Mainframe - September/October 2010 - BG-11
IBM Systems Magazine, Mainframe - September/October 2010 - BG-12
IBM Systems Magazine, Mainframe - September/October 2010 - BG-13
IBM Systems Magazine, Mainframe - September/October 2010 - BG-14
IBM Systems Magazine, Mainframe - September/October 2010 - BG-15
IBM Systems Magazine, Mainframe - September/October 2010 - BG-16
IBM Systems Magazine, Mainframe - September/October 2010 - BG-17
IBM Systems Magazine, Mainframe - September/October 2010 - BG-18
IBM Systems Magazine, Mainframe - September/October 2010 - BG-19
IBM Systems Magazine, Mainframe - September/October 2010 - BG-20
IBM Systems Magazine, Mainframe - September/October 2010 - BG-21
IBM Systems Magazine, Mainframe - September/October 2010 - BG-22
IBM Systems Magazine, Mainframe - September/October 2010 - BG-23
IBM Systems Magazine, Mainframe - September/October 2010 - BG-24
IBM Systems Magazine, Mainframe - September/October 2010 - BG-25
IBM Systems Magazine, Mainframe - September/October 2010 - BG-26
IBM Systems Magazine, Mainframe - September/October 2010 - BG-27
IBM Systems Magazine, Mainframe - September/October 2010 - BG-28
IBM Systems Magazine, Mainframe - September/October 2010 - BG-29
IBM Systems Magazine, Mainframe - September/October 2010 - BG-30
IBM Systems Magazine, Mainframe - September/October 2010 - BG-31
IBM Systems Magazine, Mainframe - September/October 2010 - BG-32
IBM Systems Magazine, Mainframe - September/October 2010 - 95
http://www.ibmsystemsmagmainframedigital.com/mspcomm/ibmsystemsmag/ibmsystems_mainframe_20200910
http://www.ibmsystemsmagmainframedigital.com/mspcomm/ibmsystemsmag/ibmsystems_mainframe_20200708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20200506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20200304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20200102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/2020mfse
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20191112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20190910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20190708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20190506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20190304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/relevantz_20190102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/2019mfse
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20190102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20181112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20180910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20180708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20180506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20180304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20180102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/2018mfse
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20171112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170910_v2
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_sesupp
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20170102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_linuxsupp
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20161112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/MainframeSecurity
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20160910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20160708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20160506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20160304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20160102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20151112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150910_se
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150910
http://www.ibmsystemsmagmainframedigital.com/MFSkills
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150506_supp
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20150102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20141112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140910_v2
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_gt_201405
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/BigData
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20140102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20131112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130910_v2
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20130102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20121112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/buyersguide2013
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20120910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20120708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20120506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20120304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20120102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/ibmsystems_mainframe_2012bg
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20111112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20110910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20110708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20110506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20110304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20110102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20101112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100910_bg
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100506
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100304
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20100102
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20091112
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20090910
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20090708
http://www.ibmsystemsmagmainframedigital.com/nxtbooks/ibmsystemsmag/mainframe_20090506
https://www.nxtbook.com/nxtbooks/ibmsystemsmag/mainframe_20090304
https://www.nxtbookmedia.com