I have written about defining RPG variables defined as SQL types in past posts, which made me curious about all there are. Having searched in IBM's Knowledge Center I was disappointed to find that there was no one page with all of this information upon. Which gave me idea to write this, a list of all SQL types available.
If I am defining variables as SQL types my source needs to be compiled as a SQLRPGLE, as the RPG SQL precompiler converts these SQL data types into more familiar RPG equivalents.
The syntax for these variable definitions is simple, it is just:
dcl-s WorkVar sqltype(Valid SQL type) ; |
There are only certain of the SQL data types that can be used. If I was define a variable with the SQL type of Variable length character, VARCHAR for example I get a "SQL precompile failure". When I look in the generated spool file I see the following:
03 dcl-s WorkVar sqltype(varchar) ;
DIAGNOSTIC MESSAGES
MSG ID SEV RECORD TEXT
SQL0104 30 3 Token VARCHAR was not valid. Valid tokens: CLOB
CLOB_LOCATOR CLOB_FILE BLOB BLOB_LOCATOR BLOB_FILE
DBCLOB_LOCATOR DBCLOB_FILE ROWID BINARY VARBINARY
RESULT_SET_LOCATOR XML_CLOB XML_BLOB XML_DBCLOB
XML_LOCATOR XML_CLOB_FILE XML_BLOB_FILE
XML_DBCLOB_FILE.
|
The SQL type can be entered in either case, in the examples below I will be using upper case only just to make it easier to read.
Let me go through all of the SQL types and show how the RPG SQL precompiler converts them to RPG compatible variables.
Binary
I fail to understand why RPG would use different binary types than SQL, but it does. There are two types of binary variables:
| Description | SQL type | Minimum length | Maximum length |
| Binary | BINARY | 1 | 3,2766 |
| Variable length binary | VARBINARY | 1 | 3,2740 |
When defining SQL binary variables I have to give a length, or the SQL precompile will fail.
01 dcl-s Binary sqltype(BINARY:9) ; 02 dcl-s VarBinary sqltype(VARBINARY:32740) ; |
When this is compiled, the compile listing shows these SQL types were translated to RPG compatible types:
//*DCL-S BINARY SQLTYPE(BINARY:9) ; DCL-S BINARY CHAR(9) CCSID(*HEX); //*DCL-S VARBINARY SQLTYPE(VARBINARY:32740) ; DCL-S VARBINARY VARCHAR(32740) CCSID(*HEX); |
Large Objects
These large objects SQL types come in three flavors:
- Binary Large OBjects (BLOB)
- Character Large OBjects (CLOB)
- Double Byte Character Large OBjects (DBCLOB)
And these are the SQL types you can define them as:
| Description | SQL type | Minimum length | Maximum length |
| Binary LOB | BLOB | 1 | 16,773,100 |
| Binary LOB locator | BLOB_LOCATOR | Needs to be >16M | |
| Character LOB | CLOB | 1 | 16,773,100 |
| Character LOB locator | CLOB_LOCATOR | Needs to be >16M | |
| Double byte LOB | DBCLOB | 1 | 8,386,550 |
| Double byte LOB locator | DBCLOB_LOCATOR | Needs to be >8M | |
The BLOB, CLOB, and DBCLOB all have to be defined with a length, the locator types do not.
03 dcl-s Blob sqltype(BLOB:1000000) ; 04 dcl-s BlobLocator sqltype(BLOB_LOCATOR) ; 05 dcl-s Clob sqltype(CLOB:1000) ; 06 dcl-s ClobLocator sqltype(CLOB_LOCATOR) ; 07 dcl-s DbClob sqltype(DBCLOB:2000) ; 08 dcl-s DbClobLocator sqltype(DBCLOB_LOCATOR) ; |
When compiled the program listing shows:
//*DCL-S BLOB SQLTYPE(BLOB:1000000) ;
DCL-DS BLOB;
BLOB_LEN UNS(10);
BLOB_DATA CHAR(1000000) CCSID(*HEX);
END-DS BLOB;
//*DCL-S BLOBLOCATOR SQLTYPE(BLOB_LOCATOR) ;
DCL-S BLOBLOCATOR UNS(10);
//*DCL-S CLOB SQLTYPE(CLOB:1000) ;
DCL-DS CLOB;
CLOB_LEN UNS(10);
CLOB_DATA CHAR(1000) CCSID(*JOBRUNMIX);
END-DS CLOB;
//*DCL-S CLOBLOCATOR SQLTYPE(CLOB_LOCATOR) ;
DCL-S CLOBLOCATOR UNS(10);
//*DCL-S DBCLOB SQLTYPE(DBCLOB:2000) ;
DCL-DS DBCLOB;
DBCLOB_LEN UNS(10);
DBCLOB_DATA GRAPH(2000);
END-DS DBCLOB;
//*DCL-S DBCLOBLOCATOR SQLTYPE(DBCLOB_LOCATOR) ;
DCL-S DBCLOBLOCATOR UNS(10);
|
Notice how the BLOB, CLOB, and DBCLOB variables have been converted into data structures consisting of two subfields:
- _LENGTH Length of the string within the data subfield
- _DATA The data itself
- IBM documentation: BLOB, CLOB, and DBCLOB
- IBM documentation: BLOB_LOCATOR, same rules apply for CLOB_LOCATOR and DBCLOB_LOCATOR
Large Objects Files
I used one of these types, Character LOB file, with SQL to copy data to a text file in the IFS.
It should come as no surprise that the three flavors of LOBs are represented here.
| Description | SQL type | Minimum length | Maximum length |
| Binary LOB file | BLOB_FILE | N/A | |
| Character LOB file | CLOB_FILE | N/A | |
| Double Byte Character LOB file | DBCLOB_FILE | N/A | |
When defining these types I do not have to give a length as these represent, rather than contain, the file.
09 dcl-s BlobFile sqltype(BLOB_FILE) ; 10 dcl-s ClobFile sqltype(CLOB_FILE) ; 11 dcl-s DbClobFile sqltype(DBCLOB_FILE) ; |
RPG's SQL precompiler translate these variables into data structures:
//*DCL-S BLOBFILE SQLTYPE(BLOB_FILE) ;
DCL-DS BLOBFILE;
BLOBFILE_NL UNS(10);
BLOBFILE_DL UNS(10);
BLOBFILE_FO UNS(10);
BLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
END-DS BLOBFILE;
//*DCL-S CLOBFILE SQLTYPE(CLOB_FILE) ;
DCL-DS CLOBFILE;
CLOBFILE_NL UNS(10);
CLOBFILE_DL UNS(10);
CLOBFILE_FO UNS(10);
CLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
END-DS CLOBFILE;
//*DCL-S DBCLOBFILE SQLTYPE(DBCLOB_FILE) ;
DCL-DS DBCLOBFILE;
DBCLOBFILE_NL UNS(10);
DBCLOBFILE_DL UNS(10);
DBCLOBFILE_FO UNS(10);
DBCLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
END-DS DBCLOBFILE;
|
The subfields of these data structures are all used the same way:
- _NL Length of the IFS path
- _DL Not used
- _FO File operation
- _NAME Path of the file in the IFS
For more information about how to use this type read the post Copying any data to and from a file in the IFS.
IBM documentation: CLOB_FILE, same rules apply for BLOB_FILER and DBCLOB_FILE
Row Identifier
Row id is an alternative to the identity column when you want to ensure uniqueness in my files. As with the identity column the value placed in the Row id is generated by the Db2 for i engine automatically. I do not want to say much more as I will write about this in a future post.
When defining a variable I do not give a length as the length of a Row id is fixed.
| Description | SQL type | Minimum length | Maximum length |
| Row identifier | ROWID | Generated by Db2 for i | |
The definition is simple:
12 dcl-s RowId sqltype(ROWID) ; |
The precompiler translate this to:
//*DCL-S ROWID SQLTYPE(ROWID) ; DCL-S ROWID VARCHAR(40) CCSID(*HEX); |
Result Set Locator
This one is an odd one. A Result Set Locator is a variable that contains the locator that identifies a stored procedure result set. Not much more I can say about that.
I do not define a length when using this type as it is system generated.
| Description | SQL type | Minimum length | Maximum length |
| Result set locator | RESULT_SET_LOCATOR | Generated by Db2 for i | |
So what does this look like:
13 dcl-s ResultSetLocator sqltype(RESULT_SET_LOCATOR) ; |
And the precompiler translate that to:
//*DCL-S RESULTSETLOCATOR SQLTYPE(RESULT_SET_LOCATOR) ; DCL-S RESULTSETLOCATOR INT(20); |
XML Host Variables
All "host variables" mean is that the XML data is hosted on my IBM i environment.
| Description | SQL type | Minimum length | Maximum length |
| XML BLOB | XML_BLOB | 1 | 16,773,100 |
| XML CLOB | XML_CLOB | 1 | 16,773,100 |
| XML Double Byte CLOB | XML_DBCLOB | 1 | 8,386,550 |
| XML locator | XML_LOCATOR | > 16M | |
I do need to give a length with the first three, but not with the locator.
14 dcl-s XmlBlob sqltype(XML_BLOB:500) ; 15 dcl-s XmlClob sqltype(XML_CLOB:5000) ; 16 dcl-s XmlDbClob sqltype(XML_DBCLOB:50000) ; 17 dcl-s XMLLocator sqltype(XML_LOCATOR) ; |
The precompiler generates:
//*DCL-S XMLBLOB SQLTYPE(XML_BLOB:500) ;
DCL-DS XMLBLOB;
XMLBLOB_LEN UNS(10);
XMLBLOB_DATA CHAR(500) CCSID(*HEX);
END-DS XMLBLOB;
//*DCL-S XMLCLOB SQLTYPE(XML_CLOB:5000) ;
DCL-DS XMLCLOB;
XMLCLOB_LEN UNS(10);
XMLCLOB_DATA CHAR(5000) CCSID(*JOBRUNMIX);
END-DS XMLCLOB;
//*DCL-S XMLDBCLOB SQLTYPE(XML_DBCLOB:50000) ;
DCL-DS XMLDBCLOB;
XMLDBCLOB_LEN UNS(10);
XMLDBCLOB_DATA UCS2(50000) CCSID(13488);
END-DS XMLDBCLOB;
|
Data structures are generated each with two subfields (like the LOB variables):
- _LENGTH Length of the string within the data subfield
- _DATA The data itself
If you are interested in more detail see Getting data from a XML file using SQL.
IBM documentation (included XML file types too)
XML File References
as with the LOB file reference types these types represent, rather than contain, the file. Therefore, when I define these types of variables I do not give them a length.
When defining these types I do not have to give a length as these represent, rather than contain, the file.
| Description | SQL type | Minimum length | Maximum length |
| XML Binary LOB file | XML_BLOB_FILE | N/A | |
| XML Character LOB file | XML_CLOB_FILE | N/A | |
| XML Double Byte Character LOB file | XML_DBCLOB_FILE | N/A | |
There definitions would look like:
18 dcl-s XMLBlobFile sqltype(XML_BLOB_FILE) ; 19 dcl-s XMLClobFile sqltype(XML_CLOB_FILE) ; 20 dcl-s XMLDbClobFile sqltype(XML_DBCLOB_FILE) ; |
Which is translated to:
//*DCL-S XMLBLOBFILE SQLTYPE(XML_BLOB_FILE) ;
DCL-DS XMLBLOBFILE;
XMLBLOBFILE_NL UNS(10);
XMLBLOBFILE_DL UNS(10);
XMLBLOBFILE_FO UNS(10);
XMLBLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
END-DS XMLBLOBFILE;
//*DCL-S XMLCLOBFILE SQLTYPE(XML_CLOB_FILE) ;
DCL-DS XMLCLOBFILE;
XMLCLOBFILE_NL UNS(10);
XMLCLOBFILE_DL UNS(10);
XMLCLOBFILE_FO UNS(10);
XMLCLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
END-DS XMLCLOBFILE;
//*DCL-S XMLDBCLOBFILE SQLTYPE(XML_DBCLOB_FILE) ;
DCL-DS XMLDBCLOBFILE;
XMLDBCLOBFILE_NL UNS(10);
XMLDBCLOBFILE_DL UNS(10);
XMLDBCLOBFILE_FO UNS(10);
XMLDBCLOBFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
END-DS XMLDBCLOBFILE;
|
The subfields of these data structures are all used the same way:
- _NL Length of the IFS path
- _DL Not used
- _FO File operation
- _NAME Path of the file in the IFS
These data structures are all used the same way. If you want to learn more see Creating a XML file in just one step using SQL.
IBM documentation (included XML host types too)
This article was written for IBM i 7.3, and should work for some earlier releases too.
much needed with modern data set processing
ReplyDelete"I fail to understand why RPG would use different binary types than SQL, but it does."
ReplyDeleteThe word "binary" in SQL means something completely different from RPG. RPG used the type "binary" long before SQL so if there's any blame to be handed out ... . RPG's type B is a legacy data type from the S/38 days that mapped to the database's binary data. Mostly it was used for integers but RPG permitted implied decimal places. With the advent of RPG IV we introduced true integers (signed - type I and unsigned - type U).
SQL binary is not for numbers. It is for the storage of bit sensitive information that is not to be subject to code page conversion. For images etc. you would normally use a BLOB for the same purpose but binaries could I guess be used for small jpegs or anything really that is bit sensitive and not a character representation.
Thank you for the explanation.
DeletePlease can you give me sql equivalent of RPG variable, as I am not able to create a sql procedure for a rpgle sub procedure in service program which returns an indicator
ReplyDeleteIndicator is not a SQL type. You will have to be a bit creative. I would define in SQL as CHAR(1).
DeleteI found this link in the IBM KnowledgeCenter
DeleteDetermining equivalent SQL and ILE RPG data types
I already tried with char(1) before posting here but no luck
DeleteBut did you define it as CHAR(1) in the RPG as well as the SQL?
DeleteStarting in 7.5, database has a BOOLEAN type which is processed as an indicator by RPG. https://www.ibm.com/docs/en/ssw_ibm_i_75/db2/rbafzboolvalues.htm, https://www.ibm.com/docs/en/ssw_ibm_i_75/rzajp/rzajpdeclaringirpg.htm
DeleteThank you Barbara for mentioning boolean type.
DeleteI wrote about them here.