You might come across a need to simulate collation sequence of DB2 on mainframe (or MVS or z/OS) as it is on DB2 LUW (Linux, Unix and Windows) platform if you want your application to show the order of the data exactly same way as it is on the mainframe.
The main reason of the different collation sequence in DB2 on z/OS and DB2 on LUW is due to the different code pages and different collation sequences. If you are using code page 037 (EBCDIC) on mainframe, you can compile and use attached C program to create your DB2 database with the same collation sequence as it is used on mainframe.
For example, if you create this table in DB2 on z/OS, you will see the following output of a query.
create table colatest(code char(2) not null, desc varchar(30)); insert into colatest values ('T','Order 01'),('TF','Order 02'),('TI','Order 03'), ('1','Order 04'),('2','Order 05'),('3','Order 06'),('3F','Order 07'), ('3I','Order 08'),('4','Order 09'),('4F','Order 10'),('4I','Order 10'), ('5','Order 11'),('6','Order 12'); SELECT * FROM COLATEST ORDER BY CODE; CODE DESC ---- ------------------------------ T Order 01 TF Order 02 TI Order 03 1 Order 04 2 Order 05 3 Order 06 3F Order 07 3I Order 08 4 Order 09 4F Order 10 4I Order 10 5 Order 11 6 Order 12
On the other hand, if you create your DB2 database with any given collation sequence (System, Identity or others), you will see a collation sequence that does not match with DB2 on z/OS. The following is a sample output from a DB2 database created with the default collation sequence.
SELECT * FROM COLATEST ORDER BY CODE; CODE DESC ---- ------------------------------ 1 Order 04 2 Order 05 3 Order 06 3F Order 07 3I Order 08 4 Order 09 4F Order 10 4I Order 10 5 Order 11 6 Order 12 T Order 01 TF Order 02 TI Order 03
Compile this C program given below to create a database with the collation sequence of CCSID 037.
#include <stdio.h> #include <stdlib.h> #include <string.h> #include <sqlutil.h> #include <sqlenv.h> #include <sqlca.h> #define USERID_SZ 128 #define PSWD_SZ 14 unsigned char sqle_819_037[256] = { 0x00,0x01,0x02,0x03,0x37,0x2d,0x2e,0x2f,0x16,0x05,0x25,0x0b,0x0c,0x0d,0x0e,0x0f, 0x10,0x11,0x12,0x13,0x3c,0x3d,0x32,0x26,0x18,0x19,0x3f,0x27,0x1c,0x1d,0x1e,0x1f, 0x40,0x5a,0x7f,0x7b,0x5b,0x6c,0x50,0x7d,0x4d,0x5d,0x5c,0x4e,0x6b,0x60,0x4b,0x61, 0xf0,0xf1,0xf2,0xf3,0xf4,0xf5,0xf6,0xf7,0xf8,0xf9,0x7a,0x5e,0x4c,0x7e,0x6e,0x6f, 0x7c,0xc1,0xc2,0xc3,0xc4,0xc5,0xc6,0xc7,0xc8,0xc9,0xd1,0xd2,0xd3,0xd4,0xd5,0xd6, 0xd7,0xd8,0xd9,0xe2,0xe3,0xe4,0xe5,0xe6,0xe7,0xe8,0xe9,0xba,0xe0,0xbb,0xb0,0x6d, 0x79,0x81,0x82,0x83,0x84,0x85,0x86,0x87,0x88,0x89,0x91,0x92,0x93,0x94,0x95,0x96, 0x97,0x98,0x99,0xa2,0xa3,0xa4,0xa5,0xa6,0xa7,0xa8,0xa9,0xc0,0x4f,0xd0,0xa1,0x07, 0x20,0x21,0x22,0x23,0x24,0x15,0x06,0x17,0x28,0x29,0x2a,0x2b,0x2c,0x09,0x0a,0x1b, 0x30,0x31,0x1a,0x33,0x34,0x35,0x36,0x08,0x38,0x39,0x3a,0x3b,0x04,0x14,0x3e,0xff, 0x41,0xaa,0x4a,0xb1,0x9f,0xb2,0x6a,0xb5,0xbd,0xb4,0x9a,0x8a,0x5f,0xca,0xaf,0xbc, 0x90,0x8f,0xea,0xfa,0xbe,0xa0,0xb6,0xb3,0x9d,0xda,0x9b,0x8b,0xb7,0xb8,0xb9,0xab, 0x64,0x65,0x62,0x66,0x63,0x67,0x9e,0x68,0x74,0x71,0x72,0x73,0x78,0x75,0x76,0x77, 0xac,0x69,0xed,0xee,0xeb,0xef,0xec,0xbf,0x80,0xfd,0xfe,0xfb,0xfc,0xad,0xae,0x59, 0x44,0x45,0x42,0x46,0x43,0x47,0x9c,0x48,0x54,0x51,0x52,0x53,0x58,0x55,0x56,0x57, 0x8c,0x49,0xcd,0xce,0xcb,0xcf,0xcc,0xe1,0x70,0xdd,0xde,0xdb,0xdc,0x8d,0x8e,0xdf }; int checkError(struct sqlca *p, char *msg) { char errorMsg[1024]; int rc; if (p->sqlcode != 0 && p->sqlcode != 100) { if ((rc = sqlaintp(errorMsg, 1024, 80, p)) > 0) { printf("Error: %s %s\n", msg, errorMsg); return -1; } } return 0; } int main(int argc, char *argv[]) { struct sqlca sqlca; int rc = 0; char nodeName[SQL_INSTNAME_SZ + 1]; char user[USERID_SZ + 1]; char pswd[PSWD_SZ + 1]; char dbName[SQL_DBNAME_SZ + 1]; char dbLocalAlias[SQL_ALIAS_SZ + 1]; char dbPath[SQL_PATH_SZ + 1]; struct sqledbdesc dbDescriptor; SQLEDBTERRITORYINFO territoryInfo; if (argc == 3 || argc == 5) { strcpy(nodeName, argv[1]); strcpy(dbName, argv[2]); if (argc == 5) { strcpy(user, argv[3]); strcpy(pswd, argv[4]); } else { strcpy(user, ""); strcpy(pswd, ""); } } else { printf("USAGE: %s [nodeName dbName [userid passwd]]\n", argv[0]); exit(-1); } if (strlen(nodeName) > 0) { printf("Attaching to the instance: %s\n", nodeName); sqleatin(nodeName, user, pswd, &sqlca); if ((rc = checkError(&sqlca, "attaching to instance")) < 0) { return rc; } } strcpy(dbLocalAlias, dbName); strcpy(dbPath, ""); strcpy(dbDescriptor.sqldbdid, SQLE_DBDESC_2); dbDescriptor.sqldbccp = 0; dbDescriptor.sqldbcss = SQL_CS_USER; memcpy(dbDescriptor.sqldbudc, sqle_819_037, SQL_CS_SZ); strcpy(dbDescriptor.sqldbcmt, "EBCDIC database collation sequence"); dbDescriptor.sqldbsgp = 0; dbDescriptor.sqldbnsg = 10; dbDescriptor.sqltsext = -1; dbDescriptor.sqlcatts = NULL; dbDescriptor.sqlusrts = NULL; dbDescriptor.sqltmpts = NULL; strcpy(territoryInfo.sqldbcodeset, "ISO8859-1"); strcpy(territoryInfo.sqldblocale, "C"); printf("Creating database %s\n", dbName); /* create database */ sqlecrea(dbName, dbLocalAlias, dbPath, &dbDescriptor, &territoryInfo, '\0', NULL, &sqlca); if ((rc = checkError(&sqlca, "creating database")) < 0) { return rc; } if (strlen(nodeName) > 0) { printf("Detaching from instance: %s\n", nodeName); sqledtin(&sqlca); if ((rc = checkError(&sqlca, "detaching from instance")) < 0) { return rc; } } return 0; }
This program was tested with following makefile on Linux and Aix 84 bit platform. If you are using IA64 bit platform, you might need to remove -m64 compile option from the makefile as gcc does not support on IA64.
LIB=lib64 CFLAG=-I$(HOME)/sqllib/include LIBFLAG=-L$(HOME)/sqllib/$(LIB) -ldb2 #### For Linux CC=gcc EXTRA_C_FLAGS=-m64 EXTRA_L_FLAGS=-Wl,-rpath,$(HOME)/sqllib/$(LIB) #### For Aix #CC=xlc #EXTRA_C_FLAGS=-q64 #EXTRA_L_FLAGS= ERASE=rm -f all : db37 clean : $(ERASE) *.o cleanall : \ clean $(ERASE) db37 db37.o : db37.c $(CC) $(EXTRA_C_FLAGS) $(CFLAG) -c db37.c db37 : db37.o $(CC) $(EXTRA_C_FLAGS) -o db37 db37.o $(EXTRA_L_FLAGS) $(LIBFLAG)
To create the database with same collation sequence as of z/OS, run this program to create the database through above program rather than creating by using CREATE DATABASE syntax.
$ ./db37 <instanceName> <nameofDatabaseTobeCreated>
Create same table as we did for z/OS and run the same query and you will see the same collation sequence that you get on z/OS.
SELECT * FROM COLATEST ORDER BY CODE; CODE DESC ---- ------------------------------ T Order 01 TF Order 02 TI Order 03 1 Order 04 2 Order 05 3 Order 06 3F Order 07 3I Order 08 4 Order 09 4F Order 10 4I Order 10 5 Order 11 6 Order 12