DB2 mainframe support UNICODE

最新推荐文章于 2022-11-11 14:21:13 发布

原创最新推荐文章于 2022-11-11 14:21:13 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

DB2 同时被 2 个专栏收录

27 篇文章

订阅专栏

mainframe

14 篇文章

订阅专栏

本文详细介绍了如何使用SQL语句创建数据库表、插入数据、查询数据，并演示了如何卸载表数据到文件，以及如何从数据文件加载数据回数据库表的过程。通过具体的例子，读者可以了解到每个步骤的具体实现。

1. Create table

CREATE TABLE TAB(
    ID             DECIMAL(4, 0)  NOT NULL,
    NAME        CHAR(06)          NOT NULL,
    ADDRESS  VARCHAR(10)     NOT NULL,
    EMAIL       VARCHAR(06),
    PHONE      CHAR(04),
    PRIMARY   KEY(ID)
) CCSID UNICODE

2 Insert data

INSERT INTO TAB(ID,NAME,ADDRESS) VALUES(1, 'AAAAAA', '文')
or
INSERT INTO TAB(ID,NAME,ADDRESS) VALUES(1, 'AAAAAA', X'E69687')

## X'E69687' is the UTF-8 coding of '文'

3. Select data

db2 => SELECT ID, NAME, ADDRESS FROM TAB
ID     NAME   ADDRESS  
------ ------ ----------
    1. AAAAAA 文      

db2 => SELECT HEX(ID), HEX(NAME), HEX(ADDRESS) FROM TAB
------ ------------ --------------------
00001C 414141414141 E69687

4. Upload table

000001 //UNLOAD   JOB (DSNTEP4),'UNLOAD',REGION=0M,CLASS=2,MSGCLASS=X,      
000002 //             NOTIFY=&SYSUID                                          
000003 //DEL      EXEC PGM=IDCAMS                                            
000004 //SYSPRINT DD SYSOUT=*                                                
000005 //SYSOUT   DD SYSOUT=*                                                
000006 //SYSIN    DD *                                                        
000007   DELETE <UID>.TEST.TAB.LOG                                          
000008   DELETE <UID>.TEST.TAB.DATA                                          
000009   DELETE <UID>.TEST.TAB.SYSPUNCH                                      
000010   IF MAXCC = 8 THEN SET MAXCC=0                                        
000011 //*------------------------------------------*                        
000012 //* LAUNCH UNLOAD UTILITY                                              
000013 //*------------------------------------------*                        
000014 //UNLOAD   EXEC DSNUPROC,UID='',UTPROC='',                            
000015 //         SYSTEM='<DSN>'                                              
000016 //SYSPRINT DD SPACE=(CYL,(1,1),RLSE),                                  
000017 //            DISP=(,CATLG),                                          
000018 //            UNIT=SYSDA,                                              
000019 //            DSN=<UID>.TEST.TAB.LOG                                  
000020 //SYSREC   DD SPACE=(CYL,(1,1),RLSE),                                  
000021 //            DISP=(,CATLG),                                          
000022 //            UNIT=SYSDA,                                              
000023 //            DSN=<UID>.TEST.TAB.DATA                                
000024 //SYSPUNCH DD SPACE=(TRK,(1,1),RLSE),                                  
000025 //            DISP=(NEW,CATLG),DCB=(LRECL=80,RECFM=FB),                
000026 //            UNIT=SYSDA,                                              
000027 //            DSN=<UID>.TEST.TAB.SYSPUNCH                            
000028 //SYSIN    DD *                                                        
000029   UNLOAD                                                                
000030   UNICODE                                                                
000031   FROM TABLE TAB;                                                        
000032 /*

5. SYSPUNCH

LOAD DATA INDDN SYSREC   LOG NO  RESUME YES
 UNICODE CCSID(00367,01208,01200)
 INTO TABLE
 "<schema>".
 "TAB"
 WHEN(00001:00002) = X'0003'
 NUMRECS                    1
 ( "ID"      POSITION(00003:00005) DECIMAL
 , "NAME"    POSITION(00006:00011) CHAR MIXED(006)
 , "ADDRESS" POSITION(00012:00023) VARCHAR MIXED
 , "EMAIL"   POSITION(00025:00032) VARCHAR MIXED NULLIF(00024)=X'FF'
 , "PHONE"   POSITION(00034:00037) CHAR MIXED(004) NULLIF(00033)=X'FF'
 )

6. Data file

$ hexdump TEST.TAB.DATA
[pad] [ ID ] [ NAME ] [LEN] [ ADDRESS ] [I][LEN] [ EMAIL ] [I][ PHONE ]
00000001: 00 03 00 00 1C 41 41 41 41 41 41 00 03 E6 96 87 20 20 20 20 20 20 20 FF 00 00 20 20 20 20 20 20 FF 20 20 20 20 : .....AAAAAA..... ... .