Host variable data types for XML data in embedded SQL applications

Db2 provides XML host variable types for assembler, C, C++, COBOL, and PL/I.

Those types are:
  • XML AS BLOB
  • XML AS CLOB
  • XML AS DBCLOB
  • XML AS BLOB_FILE (C, C++, or PL/I) or XML AS BLOB-FILE (COBOL)
  • XML AS CLOB_FILE (C, C++, or PL/I) or XML AS CLOB-FILE (COBOL)
  • XML AS DBCLOB_FILE (C, C++, or PL/I) or XML AS DBCLOB-FILE (COBOL)
The XML host variable types are compatible only with the XML column data type.

You can use BLOB, CLOB, DBCLOB, CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, BINARY, or VARBINARY host variables to update XML columns. You can convert the host variable data types to the XML type using the XMLPARSE function, or you can let the Db2 database server perform the conversion implicitly.

You can use BLOB, CLOB, DBCLOB, CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, BINARY, or VARBINARY host variables to retrieve data from XML columns. You can convert the XML data to the host variable type using the XMLSERIALIZE function, or you can let the Db2 database server perform the conversion implicitly.

The following examples show you how to declare XML host variables in each supported language. In each table, the left column contains the declaration that you code in your application program. The right column contains the declaration that Db2 generates.

Declarations of XML host variables in assembler

The following table shows assembler language declarations for some typical XML types.

Table 1. Example of assembler XML variable declarations
You declare this variable Db2 generates this variable
BLOB_XML SQL TYPE IS XML AS BLOB 1M 
BLOB_XML DS   0FL4      
BLOB_XML_LENGTH DS FL4  
BLOB_XML_DATA DS CL655351
 ORG   *+(983041)
CLOB_XML SQL TYPE IS XML AS CLOB 40000K 
CLOB_XML DS   0FL4      
CLOB_XML_LENGTH DS FL4  
CLOB_XML_DATA DS CL655351
 ORG   *+(40894465)
DBCLOB_XML SQL TYPE IS XML AS DBCLOB 4000K 
DBCLOB_XML DS   0FL4      
DBCLOB_XML_LENGTH DS FL4  
DBCLOB_XML_DATA DS GL655342
 ORG   *+(4030466)
BLOB_XML_FILE SQL TYPE IS XML AS BLOB_FILE 
BLOB_XML_FILE DS   0FL4          
BLOB_XML_FILE_NAME_LENGTH DS FL4 
BLOB_XML_FILE_DATA_LENGTH DS FL4 
BLOB_XML_FILE_FILE_OPTIONS DS FL4
BLOB_XML_FILE_NAME DS CL255
CLOB_XML_FILE SQL TYPE IS XML AS CLOB_FILE 
CLOB_XML_FILE DS   0FL4          
CLOB_XML_FILE_NAME_LENGTH DS FL4 
CLOB_XML_FILE_DATA_LENGTH DS FL4 
CLOB_XML_FILE_FILE_OPTIONS DS FL4
CLOB_XML_FILE_NAME DS CL255
DBCLOB_XML_FILE SQL TYPE IS XML AS DBCLOB_FILE 
DBCLOB_XML_FILE DS   0FL4          
DBCLOB_XML_FILE_NAME_LENGTH DS FL4 
DBCLOB_XML_FILE_DATA_LENGTH DS FL4 
DBCLOB_XML_FILE_FILE_OPTIONS DS FL4
DBCLOB_XML_FILE_NAME DS CL255
Notes:
  1. Because assembler language allows character declarations of no more than 65535 bytes, Db2 separates the host language declarations for XML AS BLOB and XML AS CLOB host variables that are longer than 65535 bytes into two parts.
  2. Because assembler language allows graphic declarations of no more than 65534 bytes, Db2 separates the host language declarations for XML AS DBCLOB host variables that are longer than 65534 bytes into two parts.

Declarations of XML host variables in C and C++

The following table shows C and C++ language declarations that are generated by the Db2 precompiler for some typical XML types. The declarations that the Db2 coprocessor generates might be different.

Table 2. Examples of C language variable declarations
You declare this variable Db2 generates this variable
SQL TYPE IS XML AS BLOB (1M) blob_xml; 

struct                    
{ unsigned long length;   
  char data??(1048576??); 
} blob_xml;
SQL TYPE IS XML AS CLOB(40000K) clob_xml; 
struct                    
{ unsigned long length;   
  char data??(40960000??);
} clob_xml;
SQL TYPE IS XML AS DBCLOB (4000K) dbclob_xml; 
struct                             
{ unsigned long length;            
  unsigned short data??(4096000??);
} dbclob_xml;
SQL TYPE IS XML AS BLOB_FILE blob_xml_file; 
struct {                   
unsigned long name_length; 
unsigned long data_length; 
unsigned long file_options;
char name??(255??);        
} blob_xml_file;
SQL TYPE IS XML AS CLOB_FILE clob_xml_file; 
struct {                   
unsigned long name_length; 
unsigned long data_length; 
unsigned long file_options;
char name??(255??);        
} clob_xml_file;
SQL TYPE IS XML AS DBCLOB_FILE dbclob_xml_file; 
struct {                   
unsigned long name_length; 
unsigned long data_length; 
unsigned long file_options;
char name??(255??);        
} dbclob_xml_file;

Declarations of XML host variables in COBOL

The declarations that are generated for COBOL differ, depending on whether you use the Db2 precompiler or the Db2 coprocessor.

The following table shows COBOL declarations that the Db2 precompiler generates for some typical XML types.

Table 3. Examples of COBOL variable declarations by the Db2 precompiler
You declare this variable Db2 precompiler generates this variable 
01  BLOB-XML USAGE IS
    SQL TYPE IS XML AS BLOB(1M).
 
01  BLOB-XML.
  02  BLOB-XML-LENGTH
      PIC 9(9) COMP.
  02  BLOB-XML-DATA.
      49  FILLER PIC X(32767).1
      49  FILLER PIC X(32767).
      Repeat 30 times
⋮
      49  FILLER
          PIC X(1048576-32*32767).
 
01  CLOB-XML USAGE IS
    SQL TYPE IS XML AS CLOB(40000K).
 
01  CLOB-XML.
  02  CLOB-XML-LENGTH
      PIC 9(9) COMP.
  02  CLOB-XML-DATA.
      49  FILLER PIC X(32767).1
      49  FILLER PIC X(32767).
      Repeat 1248 times
⋮
      49  FILLER
          PIC X(40960000-1250*32767).
 
01  DBCLOB-XML USAGE IS
  SQL TYPE IS XML AS DBCLOB(4000K).
 
01  DBCLOB-XML.
  02  DBCLOB-XML-LENGTH
      PIC 9(9) COMP.
  02  DBCLOB-XML-DATA.
      49  FILLER PIC G(32767)
            USAGE DISPLAY-1.2
      49  FILLER PIC G(32767)
            USAGE DISPLAY-1.
      Repeat 123 times
⋮
      49  FILLER
          PIC G(4096000-125*32767)
            USAGE DISPLAY-1.
 
01  BLOB-XML-FILE USAGE IS SQL
  TYPE IS XML AS BLOB-FILE.
 
01  BLOB-XML-FILE.                              
  49 BLOB-XML-FILE-NAME-LENGTH PIC S9(9) COMP-5 SYNC.
  49 BLOB-XML-FILE-DATA-LENGTH PIC S9(9) COMP-5.
  49 BLOB-XML-FILE-FILE-OPTION PIC S9(9) COMP-5.
  49 BLOB-XML-FILE-NAME PIC X(255).             
01  CLOB-XML-FILE USAGE IS SQL
    TYPE IS XML AS CLOB-FILE.
 
01  CLOB-XML-FILE.                              
  49 CLOB-XML-FILE-NAME-LENGTH PIC S9(9) COMP-5 SYNC.
  49 CLOB-XML-FILE-DATA-LENGTH PIC S9(9) COMP-5.
  49 CLOB-XML-FILE-FILE-OPTION PIC S9(9) COMP-5.
  49 CLOB-XML-FILE-NAME PIC X(255).             
01  DBCLOB-XML-FILE USAGE IS SQL
    TYPE IS XML AS DBCLOB-FILE.
 
01  DBCLOB-XML-FILE.                                   
  49 DBCLOB-XML-FILE-NAME-LENGTH PIC S9(9) COMP-5 SYNC.
  49 DBCLOB-XML-FILE-DATA-LENGTH PIC S9(9) COMP-5.     
  49 DBCLOB-XML-FILE-FILE-OPTION PIC S9(9) COMP-5.     
  49 DBCLOB-XML-FILE-NAME PIC X(255).
Notes:
  1. For XML AS BLOB or XML AS CLOB host variables that are greater than 32767 bytes in length, Db2 creates multiple host language declarations of 32767 or fewer bytes.
  2. For XML AS DBCLOB host variables that are greater than 32767 double-byte characters in length, Db2 creates multiple host language declarations of 32767 or fewer double-byte characters.

Declarations of XML host variables in PL/I

The declarations that are generated for PL/I differ, depending on whether you use the Db2 precompiler or the Db2 coprocessor.

The following table shows PL/I declarations that the Db2 precompiler generates for some typical XML types.

Table 4. Examples of PL/I variable declarations
You declare this variable Db2 precompiler generates this variable 
DCL BLOB_XML
  SQL TYPE IS XML AS BLOB (1M);
 
DCL                                            
   1   BLOB_XML,                               
      2   BLOB_XML_LENGTH BIN FIXED(31),       
      2   BLOB_XML_DATA,1
        3   BLOB_XML_DATA1 (32) CHAR(32767),
        3   BLOB_XML_DATA2 CHAR(32);        

DCL CLOB_XML
  SQL TYPE IS XML AS CLOB (40000K);
 
DCL                                            
   1   CLOB_XML,                               
      2   CLOB_XML_LENGTH BIN FIXED(31),       
      2   CLOB_XML_DATA,1                       
        3   CLOB_XML_DATA1 (1250) CHAR(32767),
        3   CLOB_XML_DATA2 CHAR(1250);      
DCL DBCLOB_XML
  SQL TYPE IS XML AS DBCLOB (4000K);
 
DCL                                                  
   1   DBCLOB_XML,                                   
      2   DBCLOB_XML_LENGTH BIN FIXED(31),           
      2   DBCLOB_XML_DATA,2                           
        3   DBCLOB_XML_DATA1 (250  ) GRAPHIC(16383), 
        3   DBCLOB_XML_DATA2 GRAPHIC(250);           

DCL BLOB_XML_FILE
  SQL TYPE IS XML AS BLOB_FILE;
 
DCL                                                       
   1   BLOB_XML_FILE,                                     
      2   BLOB_XML_FILE_NAME_LENGTH BIN FIXED(31) ALIGNED,
      2   BLOB_XML_FILE_DATA_LENGTH BIN FIXED(31),        
      2   BLOB_XML_FILE_FILE_OPTIONS BIN FIXED(31),       
      2   BLOB_XML_FILE_NAME CHAR(255);                   
DCL CLOB_XML_FILE
  SQL TYPE IS XML AS CLOB_FILE;
 
DCL                                                        
   1   CLOB_XML_FILE,                                      
      2   CLOB_XML_FILE_NAME_LENGTH BIN FIXED(31) ALIGNED, 
      2   CLOB_XML_FILE_DATA_LENGTH BIN FIXED(31),         
      2   CLOB_XML_FILE_FILE_OPTIONS BIN FIXED(31),        
      2   CLOB_XML_FILE_NAME CHAR(255);                    
DCL DBCLOB_XML_FILE SQL TYPE IS XML AS 
  DBCLOB_FILE;
 
DCL                                                         
   1   DBCLOB_XML_FILE,                                     
      2   DBCLOB_XML_FILE_NAME_LENGTH BIN FIXED(31) ALIGNED,
      2   DBCLOB_XML_FILE_DATA_LENGTH BIN FIXED(31),        
      2   DBCLOB_XML_FILE_FILE_OPTIONS BIN FIXED(31),       
      2   DBCLOB_XML_FILE_NAME CHAR(255);
Notes:
  1. For XML AS BLOB or XML AS CLOB host variables that are greater than 32767 bytes in length, Db2 creates host language declarations in the following way:
    • If the length of the XML is greater than 32767 bytes and evenly divisible by 32767, Db2 creates an array of 32767-byte strings. The dimension of the array is length/32767.
    • If the length of the XML is greater than 32767 bytes but not evenly divisible by 32767, Db2 creates two declarations: The first is an array of 32767 byte strings, where the dimension of the array, n, is length/32767. The second is a character string of length length-n*32767.
  2. For XML AS DBCLOB host variables that are greater than 16383 double-byte characters in length, Db2 creates host language declarations in the following way:
    • If the length of the XML is greater than 16383 characters and evenly divisible by 16383, Db2 creates an array of 16383-character strings. The dimension of the array is length/16383.
    • If the length of the XML is greater than 16383 characters but not evenly divisible by 16383, Db2 creates two declarations: The first is an array of 16383 byte strings, where the dimension of the array, m, is length/16383. The second is a character string of length length-m*16383.