Table of contents

Table of contents. 1

Data conversion methods on iSeries. 1

Control language commands: 1

Copy File (CPYF) 1

Copy (CPY) 3

Stream file. 13

Copy from stream file CPYFRMSTMF. 15

Copy to stream file CPYTOSTMF. 23

Import file. 32

Copy from import file CPYFRMIMPF. 34

Copy to import file CPYTOIMPF. 41

PC documents. 50

Copy from PC document CPYFRMPCD.. 54

Copy to PC document CPYTOPCD.. 64

Comparison of Control language data manipulation commands. 70

File Transfer Protocol (FTP) 77

iSeries FTP client 79

Personal Computer FTP client 87

Client Access. 93

Enable Bidirectional script transformations for Client Access. 95

Data transfer from the AS/400. 104

Data transfer to the AS/400. 111

HTTP server. 120

HTTP Server (powered by Apache) 131

The original HTTP server. 182

IBM Tool Box For Java. 193

What is IBM Tool Box For Java?. 194

Accessing a Database file on iSeries using com.ibm.as400.access.AS400JDBCDriver. 197

Example for accessing an iSeries database file. 229

Migration guidelines. 368

Recommendations for Bidi CCSID settings on the iSeries. 369

System values. 372

Which CCSID should I use?. 391

 

Data conversion methods on iSeries

The OS/400 supports CCSID conversions on different commands and applications. The next sections relate to CCSID conversions at different levels in the operating system.

Control language commands:

Changing the CCSID value associated to a file does not modify the layout of the data content. Use one of the following copy commands to modify the data layout between different file CCSIDs:

Copy File (CPYF)

The Copy File (CPYF) command copies all or part of a database or external device file to a database or external device file. See CPYF (Copy File) Command in the CL Reference for more information.

Use the command copy file CPYF parameter “Record format field mapping” FMTOPT(*MAP) to convert data stored in a Physical file from one CCSID to another. The data is converted from CCSID1 associated with the source file or field to CCSID2 associated with the target file or field.

If *MAP is specified and a valid conversion is defined between the from-file field CCSID1 and the to-file field CCSID2, the character data is converted to the to-file field CCSID2.

If the from-file field CCSID or the to-file field CCSID is 65535, the character data is not converted.

Assign to the parameter “Record format field mapping FMTOPT” the value *MAP. See Figure 1: Copy file command prompt CPYF ... FMTOPT(*MAP)

Figure 1: Copy file command prompt CPYF ... FMTOPT(*MAP)Text Box:                                 Copy File (CPYF)                                
                                                                                
 Type choices, press Enter.                                                     
                                                                                
 Include records by field test:   INCREL                                        
   Relationship . . . . . . . . .                *NONE                          
   Field  . . . . . . . . . . . .                                               
   Relational operator  . . . . .                                               
   Value  . . . . . . . . . . . .                                               
                                                                                
                                                                                
                                                                                
                           + for more values                                    
 Record format field mapping  . . FMTOPT         *MAP                           
                                                                                
 Source update options  . . . . . SRCOPT         *SAME                          
                                                                                
 Source sequence numbering:       SRCSEQ                                        
   Starting sequence number . . .                1.00                           
   Increment number . . . . . . .                1.00                           
                                                                        More... 
 F3=Exit   F4=Prompt   F5=Refresh   F12=Cancel   F13=How to use this display    
 F24=More keys

 


Copy (CPY)

The Copy (CPY) command copies a single object or a group of objects. See CPY Command Description in the CL Reference for more information.

 

 

The Bidi layout engine is invoked to perform code page and layout conversion, from CCSID (FROMCCSID) to CCSID (TOCCSID), see Figure 2: Copy CPY command prompt.

The conversion from FROMCCSID to TOCCSID must be a valid combination supported by the operating system (refer to valid CCSID combinations: What maps are supported in V5R1M0).

For example, if the FROMCCSID associated with OBJECT1 is 420, and the TOCCSID associated with OBJECT2 is 1256, the Bidi layout engine converts the data from CCSID 420 to CCSID 1256 and stores the resulting data in the target object.

Other example: if the FROMCCSID associated with OBJECT1 is 424, and the TOCCSID associated with OBJECT2 is 1255, the Bidi layout engine converts the data from CCSID 424 to CCSID 1255 and stores the resulting data in the target object.

Figure 2: Copy CPY command promptText Box:                                 Copy Object (CPY)                               
                                                                                
 Type choices, press Enter.                                                     
                                                                                
 Object . . . . . . . . . . . . . OBJ          > '/source_directory/source_objec
t'                                                                              
 To directory . . . . . . . . . . TODIR          '.'                            
                                                                                
 To object  . . . . . . . . . . . TOOBJ        > target_object                  
                                                                                
 Symbolic link  . . . . . . . . . SYMLNK         *NO                            
 From CCSID . . . . . . . . . . . FROMCCSID      *OBJ                           
 To CCSID . . . . . . . . . . . . TOCCSID        *OBJ                           
 Data Format  . . . . . . . . . . DTAFMT         *BINARY                        
 Directory subtree  . . . . . . . SUBTREE        *NODIR                         
 Replace object . . . . . . . . . REPLACE        *NO                            
 Owner  . . . . . . . . . . . . . OWNER          *NEW                           
                                                                                
                                                                                
                                                                                
                                                                         Bottom 
 F3=Exit   F4=Prompt   F5=Refresh   F12=Cancel   F13=How to use this display    
 F24=More keys

 


Stream file

A stream file is a randomly accessible sequence of bytes, with no further structure imposed by the system. For more information about integrated file system commands, see the Integrated File System Introduction topic in the Information Center.

Copy from stream file CPYFRMSTMF

The Copy from Stream File (CPYFRMSTMF) command copies the data in a stream file to a database file member. Optional conversion of the data, reordering and shaping could be performed when copying a database file member. See CPYFRMSTMF Command Description in the CL Reference for more information.

If the Data Conversion options field (CVTDTA) is set to *AUTO, the Bidi layout engine is invoked to perform code page and layout conversion, from the Stream file code page (STMFCODPAG) to Database file CCSID (DBFCCSID). See Figure 3: Copy from stream file CPYFRMSTMF command prompt.

The conversion from STMFCODPAG to DBFCCSID must be a valid combination supported by the operating system (refer to valid CCSID combinations: What maps are supported in V5R1M0).

For example, if the code page associated with the stream file is 1256, and the CCSID associated with the database file is 420 the Bidi layout engine converts the data from a stream file with CCSID 1256 to a data base file with CCSID 420 and stores the resulting data in the target database file.

Other example: if the code page associated with the stream file is 1255, and the CCSID associated with the database file is 424 the Bidi layout engine converts the data from a stream file with CCSID 1255 to a data base file with CCSID 424 and stores the resulting data in the target database file.

 

Figure 3: Copy from stream file CPYFRMSTMF command promptText Box:                        Copy From Stream File (CPYFRMSTMF)                       
                                                                                
 Type choices, press Enter.                                                     
                                                                                
 From stream file . . . . . . . . FROMSTMF       /srcdir/sourcefile.txt         
                                                                                
 To file member or save file  . . TOMBR          /QSYS.LIB/TRGLIB.LIB/TRGFIL.FIL
E/TRGMBR.MBR                                                                    
 Member option  . . . . . . . . . MBROPT         *NONE                          
 Data conversion options  . . . . CVTDTA         *AUTO                          
 Stream file code page  . . . . . STMFCODPAG     *STMF                          
 Database file CCSID  . . . . . . DBFCCSID       *FILE                          
 Conversion table . . . . . . . . TBL                                           
                                                                                
 End of line characters . . . . . ENDLINFMT      *ALL                           
 Tab character expansion  . . . . TABEXPN        *YES                           
                                                                                
                                                                                
                                                                                
                                                                                
                                                                         Bottom 
 F3=Exit   F4=Prompt   F5=Refresh   F12=Cancel   F13=How to use this display    
 F24=More keys

Copy to stream file CPYTOSTMF

The Copy to Stream File (CPYTOSTMF) command copies either a database file member or a save file to a stream file. Optional conversion of the data, reordering and shaping could be performed when copying a database file member. See CPYTOSTMF Command Description in the CL Reference for more information.

If the Data Conversion options (CVTDTA) field is set to *AUTO, the Bidi layout engine is invoked to perform code page and layout conversion, from the Database file CCSID (DBFCCSID) to the Stream file code page (STMFCODPAG). See Figure 4: Copy to stream file CPYTOSTMF command prompt

The conversion from DBFCCSID to STMFCODPAG must be a valid combination supported by the operating system (refer to valid CCSID combinations: What maps are supported in V5R1M0).

For example, if the CCSID associated with the database file is 420, and the code page associated with the stream file is 1256, the Bidi layout engine converts the data from a data base file with CCSID 420 to a stream file with CCSID 1256 and stores the resulting data in the target stream file.

Other example: if the CCSID associated with the database file is 424, and the code page associated with the stream file is 1255, the Bidi layout engine converts the data from a data base file with CCSID 424 to a stream file with CCSID 1255 and stores the resulting data in the target stream file.

 

Figure 4: Copy to stream file CPYTOSTMF command promptText Box:                          Copy To Stream File (CPYTOSTMF)                        
                                                                                
 Type choices, press Enter.                                                     
                                                                                
 From file member or save file  . FROMMBR      > /QSYS.LIB/SRCLIB.LIB/SRCFIL.FIL
E/SRCMBR.MBR                                                                    
 To stream file . . . . . . . . . TOSTMF       > /trgdir/targetfile.txt         
                                                                                
 Stream file option . . . . . . . STMFOPT        *NONE                          
 Data conversion options  . . . . CVTDTA         *AUTO                          
 Database file CCSID  . . . . . . DBFCCSID       *FILE                          
 Stream file code page  . . . . . STMFCODPAG     *STMF                          
 Conversion table . . . . . . . . TBL                                           
                                                                                
 End of line characters . . . . . ENDLINFMT      *CRLF                          
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                         Bottom 
 F3=Exit   F4=Prompt   F5=Refresh   F12=Cancel   F13=How to use this display    
 F24=More keys

Import file

You can import or export data files between different platforms. See Copying between different systems for more details.

Copy from import file CPYFRMIMPF

The Copy from import File (CPYFRMIMPF) command copies the data in an import file (stream, physical, logical or DDM) to a database file member. Optional conversion of the data and reformatting is performed when copying a database file member. See CPYFRMIMPF Command Description in the CL Reference for more information.

Specify the import file CCSID in the parameter (FROMCCSID). The Bidi layout engine is invoked to perform code page and layout conversion, from the import file code page (FROMCCSID) to the Database file CCSID. See : Copy from import file CPYFRMIMPF command prompt.

The conversion from the CCSID specified in parameter STMFCODPAG to CCSID specified in the parameter DBFCCSID must be a valid combination supported by the operating system (refer to valid CCSID combinations: What maps are supported in V5R1M0).

For example: if the code page associated with the import file is 1256, and the CCSID associated with the database file is 420, the Bidi layout engine converts the data from the import file CCSID 1256 to the data base file CCSID 420 and stores the resulting data in the target database file.

Other example: if the code page associated with the import file is 1255, and the CCSID associated with the database file is 424, the Bidi layout engine converts the data from the import file CCSID 1255 to the data base file CCSID 424 and stores the resulting data in the target database file.

Text Box:                       Copy From Import File (CPYFRMIMPF)                       
                                                                               
Type choices, press Enter.                                                     
                                                                               
From stream file . . . . . . . . FROMSTMF     > '/sourcedir/sourcefile.txt'    
                                                                               
From file:                       FROMFILE                                      
  File . . . . . . . . . . . . .                                               
    Library  . . . . . . . . . .                  *LIBL                        
  Member . . . . . . . . . . . .                *FIRST                         
To data base file:               TOFILE                                        
  File . . . . . . . . . . . . .              > TRGFIL                         
    Library  . . . . . . . . . .              >   TRGLIB                       
  Member . . . . . . . . . . . .              > TRGMBR                         
Replace or add records . . . . . MBROPT         *ADD                           
Stream file record length  . . . STMFLEN        *TOFILE                        
From CCSID . . . . . . . . . . . FROMCCSID    > CCSID1                         
Record delimiter . . . . . . . . RCDDLM         *EOR                           
Record format of import file . . DTAFMT         *DLM                           
String delimiter . . . . . . . . STRDLM         '"'                            
                                                                       More... 
F3=Exit   F4=Prompt   F5=Refresh   F12=Cancel   F13=How to use this display    
F24=More keys
Figure 5: Copy from import file CPYFRMIMPF command prompt

Copy to import file CPYTOIMPF

The Copy to Import File (CPYTOIMPF) command copies an externally described file to an import file. Optional conversion of the data and reformatting is performed when copying a database file member. See CPYTOIMPF Command Description in the CL Reference for more information.

Specify the import file CCSID in the parameter (STMFCODPAG). The Bidi layout engine is invoked to perform code page and layout conversion, from the database file CCSID to the import file code page (TOCCSID). See Figure 6: Copy to import file CPYTOIMPF command prompt.

The conversion from the database file CCSID to import file CCSID must be a valid combination supported by the operating system (refer to valid CCSID combinations: What maps are supported in V5R1M0).

For example: if the CCSID associated with the database file is 420, and the code page associated with the import file is 1256, the Bidi layout engine converts the data from the data base file CCSID 420 to the import file CCSID 1256 and stores the resulting data in the target import file.

Other example: if the CCSID associated with the database file is 424, and the code page associated with the import file is 1255, the Bidi layout engine converts the data from the data base file CCSID 424 to the import file CCSID 1255 and stores the resulting data in the target import file.

Figure 6: Copy to import file CPYTOIMPF command prompt

Text Box:                          Copy To Import File (CPYTOIMPF)                        
                                                                                
 Type choices, press Enter.                                                     
                                                                                
 From file:                       FROMFILE                                      
   File . . . . . . . . . . . . .              > TRGFIL                         
     Library  . . . . . . . . . .              >   TRGLIB                       
   Member . . . . . . . . . . . .              > TRGMBR                         
 To data base file:               TOFILE                                        
   File . . . . . . . . . . . . .                                               
     Library  . . . . . . . . . .                  *LIBL                        
   Member . . . . . . . . . . . .                *FIRST                         
 To stream file . . . . . . . . . TOSTMF       > '/targetdirectory/taregetfile.t
xt'                                                                             
 Replace or add records . . . . . MBROPT         *ADD                           
 Stream file code page  . . . . . STMFCODPAG     CCSID2                         
 Record delimiter . . . . . . . . RCDDLM         *CRLF                          
 Record format of import file . . DTAFMT         *DLM                           
                                                                                
                                                                                
                                                                         Bottom 
 F3=Exit   F4=Prompt   F5=Refresh   F12=Cancel   F13=How to use this display    
 F24=More keys

 

 


PC documents

PC documents are stored in the Document Library Services file system QDLS. The PC documents are not associated with a specific CCSID; most of the PC documents expect implicit data. Converting data from or to a PC document involves a conversion table only, no layout processing occurs to the converted data. The Bidi layout engine is not involved in such an operation. In order for the conversion to succeed you need to:

  1. Use a physical file associated to an implicit CCSID (String type 5 or 6).
  2. Use the proper conversion table to convert from the PC Document code page to the Physical file code page.

Copy from PC document CPYFRMPCD

The Copy from PC Document (CPYFRMPCD) command copies the data in a PC document in a folder to a system physical database file member. See CPYFRMPCD Command Description in the CL Reference for more information.

 

Code page conversion occurs based on the conversion table specified in the Translate table (TRNTBL) parameter; see

Figure 7: Copy from PC document CPYFMRPCD command prompt. No layout conversion occurs, as the Bidi layout engine is not invoked.

Specify a conversion table name in the TRNTBL parameter, to convert from the PC document code page to the Physical file. If the PC document data is implicit then the Physical file must be tagged with an implicit CCSID associated to string type 5 or 6.

 

Text Box:                         Copy From PC Document (CPYFRMPCD)                       
                                                                                
 Type choices, press Enter.                                                     
                                                                                
 From folder  . . . . . . . . . . FROMFLR      > PCFOLDER                       
                                                                                
 To file  . . . . . . . . . . . . TOFILE       > TOFIL                          
   Library  . . . . . . . . . . .              >   TOLIB                        
 From document  . . . . . . . . . FROMDOC      > PCFILE.TXT                     
 To member  . . . . . . . . . . . TOMBR          *FIRST                         
 Replace or add records . . . . . MBROPT         *REPLACE                       
 Translate table  . . . . . . . . TRNTBL       > T12560420                      
   Library  . . . . . . . . . . .              >   *LIBL                        
 Format of PC data  . . . . . . . TRNFMT         *TEXT                          
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                                
                                                                         Bottom 
 F3=Exit   F4=Prompt   F5=Refresh   F12=Cancel   F13=How to use this display    
 F24=More keys
Figure 7: Copy from PC document CPYFMRPCD command prompt

 

 


Copy to PC document CPYTOPCD

The Copy to PC Document (CPYTOPCD) command copies a member from a system database file to a PC document in a folder. See CPYTOPCD Command Description in the CL Reference for more information.

Code page conversion occurs based on the conversion table specified in the Translate table (TRNTBL) parameter, see Figure 8: Copy to PC document CPYTOPCD command prompt. No layout conversion occurs, as the Bidi layout engine is not invoked.

Specify a conversion table name in the TRNTBL parameter, to convert from the Physical file to the PC document code page. If the PC document data is implicit then the Physical file must be tagged with an implicit CCSID associated to string type 5 or 6.

Text Box:                          Copy To PC Document (CPYTOPCD)                         
                                                                                
 Type choices, press Enter.                                                     
                                                                                
 From file  . . . . . . . . . . . FROMFILE     > FRMFIL                         
   Library  . . . . . . . . . . .              >   FRMLIB                       
 To folder  . . . . . . . . . . . TOFLR        > PCFOLDER                       
                                                                                
 From member  . . . . . . . . . . FROMMBR        *FIRST                         
 To document  . . . . . . . . . . TODOC        > PCFILE.TXT                     
 Replace document . . . . . . . . REPLACE        *NO                            
 Translate table  . . . . . . . . TRNTBL         q420bfa97                      
   Library  . . . . . . . . . . .                  qusrsys                      
 Format of PC data  . . . . . . . TRNFMT         *TEXT                          
                                                                                
                            Additional Parameters                               
                                                                                
 Record format  . . . . . . . . . RCDFMT         *ALL                           
                                                                                
                                                                                
                                                                         Bottom 
 F3=Exit   F4=Prompt   F5=Refresh   F12=Cancel   F13=How to use this display    
 F24=More keys
 


Figure 8: Copy to PC document CPYTOPCD command prompt

Comparison of Control language data manipulation commands

The following table compares the CL commands mentioned earlier from the point of view of Bidi:

Comparison

CPYF

CPY

CPYFRMSTMF CPYTOSTMF

CPYFRMIMPF CPYTOIMPF

CPYFRMPCD CPYTOCPD

Performs code page conversion

YES

YES

YES

YES

YES

Performs reordering and shaping

YES

YES

YES

YES

NO

Reordering observes field boundary

YES

YES for physical files

NO for stream files

NO

YES

NO

 

File Transfer Protocol (FTP)

You can set up your iSeries system to send, receive, and share files across networks by using the File Transfer Protocol (FTP). For information about FTP, refer to Getting started with FTP.

iSeries FTP client

You could start the FTP session from the iSeries. For more details refer to Working with the FTP client

Log on to the target FTP server, then specify the file transfer type or the representation in which the transfer is to take place on the local system using the FTP command LTYPE. So conversion occurs on the iSeries/400 from the CCSID associated to the iSeries file to the CCSID specified in the LTYPE command.

Example:

If the FTP client runs on an iSeries and the FTP server runs on an Arabic AIX system, then specify the CCSID of the target system

ltype c 1089

Then issue the rest of the FTP commands such as put or get.

To specify the file transfer type use the TYPE command.

Personal Computer FTP client

To exchange data between the FTP client on a personal computer and the FTP server on the iSeries, you need to define the client CCSID using the QUOTE command, then you issue the FTP commands get or put.

Example:

If the PC client is running Arabic Windows, the client CCSID is 1256.  On the PC FTP client session issue the command:

quote type c 1256

Then issue the rest of the FTP commands such as put or get.

Client Access

You could exchange data between the iSeries and Windows client using the Client Access Express V5R1. To enable the Bidi transformation for client access execute the Enable Bidirectional script transformations for Client Access procedure once for each client

Enable Bidirectional script transformations for Client Access

Arabic and Hebrew are examples of languages that use bidirectional scripts. On the server, bidirectional scripts are typically stored in the 'visual' form. On Windows platforms, bidirectional scripts are stored in the 'logical' form. This option enables the transformation between the logical and visual forms.

 

  1. From the Windows Control Panel select the Client or iSeries Access for Windows setup.
  2. Select the language Tab.
  3. The Check box “Enable bidirectional script transformation” controls the Bidi transformation. This option should be checked to enable the Bidirectional script transformation on the Windows client.

 

 

Data transfer from the AS/400

Activate the Data transfer from AS/400 by clicking on the icon  that you can find in the Client Access folder.

Select the source file on the AS/400 and target file on the PC.

Then click the Details button and set the “Translate EBCDIC to:” list item to “ANSI”.

 

Data transfer to the AS/400

Activate the Data transfer to AS/400 by clicking on the icon  that you can find in the Client Access folder.

Select the source file on the AS/400 and target file on the PC.

Then click the Details button and set the “Translate from:” list item to “ANSI”.

Note:

If the file transferred from the PC contains Substitution control characters (hexadecimal value ’1A’), the file transfer operation will stop prematurely as the ‘1A’ character was used for End Of File in the old days of DOS. Make sure that the input PC file does not contain the character x’1A’ to ensure that the transfer operation will be completed.

 

HTTP server

 

Hyper Text Transfer Protocol (HTTP) is the protocol used to communicate between a client (browser) and your Web server. We discuss the basic steps to set the CCSID in the HTTP server.

 

There are two flavors of HTTP server:

  1. HTTP Server (powered by Apache).
  2. The original HTTP server.

You can configure and administer HTTP server instances from a Web browser. To access the configuration and administration screen, type the following URL from a Web browser:

http://hostname:2001

Where hostname is the name or IP address of the HTTP server machine.

For more information refer to IBM HTTP server for iSeries documentation.

HTTP Server (powered by Apache)

 

To change the CCSID associated to the HTTP server (powered by Apache) instance, apply the following steps:

  

1.     Start the HTTP Administration server (*ADMIN).

·             From the Operations Navigator expand Network -> Servers -> TCP/IP and right-click HTTP Administration. Then click Start.

·             Or, from the iSeries command line type:

STRTCPSVR SERVER(*HTTP) HTTPSVR(*ADMIN)

 

2.     Verify that the *ADMIN server is up and running.

·             From the Operations Navigator expand Network -> Servers -> TCP/IP. On the right panel the HTTP Administration server status should be “Started”.

·             Or, from the iSeries command line type:

WRKACTJOB SBS(QHTTPSVR)

As soon as both ADMIN jobs have reached the sign wait (SIGW) status as shown in Figure 9, you are ready to go. Note that the startup time may take a minute.

 

Text Box: Work with Active Jobs
     										        02/06/04
CPU %:       1,0	         Elapsed time:     02:13:41		   Active jobs:  203


Type options, press Enter.
    2=Change	3=Hold	4=End		5=Work with	6=Release	7=Display message
    8=Work with spooled files			13=Disconnect...


Opt	Subsystem/Job	User	   	   Type	    CPU %	Function	 	 Status
__	QHTTPSVR		QSYS		   SBS		0,0				   DEQW
__	    ADMIN		QTMHHTTP	   BCH		0,0	PGM-QZHBHTTP	   SIGW
__	    ADMIN		QTMHHTTP	   BCI		0,1	PGM-QZSRHTTP	   SIGW
__	    ADMIN		QTMHHTTP	   BCI		0,0	PGM-QYUNLANG	   TIMW
__	    ADMIN		QTMHHTTP	   BCI		0,0	PGM-QYUNLANG	   TIMW





Parameters or command
===>________________________________________________________________________________
F3=Exit 	F5=Refresh			F7=Find		F10=Restart statistics
F11=Display elapsed date		F12=Cancel		F23=More options	F24=More

  Figure 9: WRKACTJOB SBS(QHTTPSVR) shows ADMIN server ready and waiting for work

 


3.     Open a Web browser and type the following URL:

http://your.server.name:2001

Where your.server.name is the name or the IP address of your iSeries server.

 

4.     Enter your User ID and password as shown in Figure 10.

 

Figure 10: Enter OS/400 User ID and password when prompted

 

5.     The AS/400 Tasks page appears as shown in Figure 11.

 

Figure 11:  AS/400 Tasks page for your iSeries

 

6.     On the AS/400 Tasks page select

IBM HTTP Server for AS/400 -> Configuration and Administration -> Configuration tab. 

The HTTP server instance configuration appears as shown in Figure 12.

 

 

Figure 12: HTTP server (powered by Apache) configuration panel 

 

 

7.     Under the Web Site Definition menu in Figure 12, select General Settings.

This brings a new window as shown in Figure 13.

 

Figure 13: Changing Server CCSID field

8.     Set the Server CCSID field to:

a.      37 For English.

b.     425 or 62251 For Arabic. (Note: Those CCSIDs are not supported yet)

c.      424, 62211, or 62235 For Hebrew.

      Then start the server.

Note: After each time the server CCSID is changed, the server must be explicitly stopped and started. Restarting the running HTTP server instance does not reflect the CCSID changes.

 

The original HTTP server

 

To change the CCSID associated to the original HTTP server instance, use the following command on the iSeries command line:

 

STRTCPSVR SERVER(*HTTP) HTTPSVR(DEFAULT '-fsccsid 37')

Where DEFAULT is the instance name, this command is shown in Figure 14.

 

Changing the instance CCSID from the browser does not reflect the changes.

 


Text Box:                        Start TCP/IP Server (STRTCPSVR)                        
                                                                               
Type choices, press Enter.                                                                                                                                 
Server application . . . . . . . > *HTTP         *ALL, *SNMP, *ROUTED...       
               + for more values                                               
Restart server . . . . . . . . .   *NONE         *NONE, *HTTP, *DNS, *DHCP     
HTTP server:                                                                   
  Server instance  . . . . . . . > DEFAULT       Name, *ALL, *ADMIN            
  Instance startup values  . . . > '-fsccsid 37'     


F3=Exit   F4=Prompt   F5=Refresh   F12=Cancel   F13=How to use this display 
F24=More keys                                                               
                          
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                               
                                                                        Bottom 
F3=Exit   F4=Prompt   F5=Refresh   F12=Cancel   F13=How to use this display    
F24=More keys
Figure 14: STRTCPSVR SERVER(*HTTP) HTTPSVR(DEFAULT '-fsccsid 37')

 


IBM Tool Box For Java

What is IBM Tool Box For Java?

 

IBM Toolbox for Java is a set of Java™ classes that allow you to use Java programs to access data on your iSeries and AS/400e servers. You can use these classes to write client/server applications, applets, and servlets that work with data on your iSeries. You can also run Java applications that use the IBM Toolbox for Java classes on the iSeries Java virtual machine (JVM). For more information visit the information center. You can also view the Javadocs for IBM Toolbox for Java classes.

Accessing a Database file on iSeries using com.ibm.as400.access.AS400JDBCDriver

To create a Java program that connects to iSeries, accesses a database file and manipulates data stored in it using AS400JDBCDriver, follow these steps:

1.     Import the AS400JDBCDriver class contained in jt400.jar and the JDBC package.

o       import com.ibm.as400.access.AS400JDBCDriver;

o       import java.sql.*;

2.     Register the JDBC driver with the DriverManager.

o       DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());

3.     Connect to an iSeries database through the DriverManager.

o       Connection c = DriverManager.getConnection("jdbc:as400:// [system name] "); where [system name] is your iSeries system IP.

4.     Create the SQL statements that you want to use to update the data in your table. SQL statements can be any of these types:

o       Statement is the standard SQL statement interface.

o       PreparedStatement is a precompiled statement that can accept input parameters.

o       CallableStatement is a precompiled statement that can accept input and output parameters.

Note: The CCSID of the database table can be set to one of the Bidi CCSIDs in the “CREATE TABLE” SQL statement within the code as you see in the following statement.

 CREATE TABLE MYLIBRARY.MYTABLE (NAME VARCHAR(20) CCSID Bidi_CCSID NOT NULL WITH DEFAULT, ID INTEGER)

5.     Set IN parameters and register OUT parameters for your PreparedStatement and CallableStatement SQL statements.  This technique is used to insert the Bidi strings into the statement without affecting the SQL statement order.  In order to understand this see the following:

·       INSERT INTO MYLIBRARY.MYTABLE2 (NAME, ID, ADDRESS) VALUES (‘ABC’, 123, ‘XYZ’ ) //This is how it is typed.

·       INSERT INTO MYLIBRARY.MYTABLE2 (NAME, ID, ADDRESS) VALUES (‘ZYX’, 123, ‘CBA’ ) //This is how it is displayed.

Where: ABC and XYZ capital letters represent Bidi strings.

This affects the result of the SQL statement execution, so using the “setString” method in the PreparedStatement class or the CallableStatement helps using Arabic or Hebrew words in SQL statements. This method takes two arguments: the first is the string index in the SQL statement and the second is the string to be inserted. Thus, the Bidi string is inserted in its appropriate position in the SQL statement.

 

                        Example:

           

Connection c = DriverManager.getConnection("jdbc:as400://iSeries_system_IP", username, password);

PreparedStatement ps = c. prepareStatement ("INSERT INTO MYLIBRARY.MYTABLE2 (NAME, ID, ADDRESS) VALUES (?, 123, ?)");

ps.setString(1, “CBA”);          //First parameter, i.e. it will be inserted instead of the first question mark.

ps.setString(2, “ZYX”);          //Second parameter, i.e. it will be inserted instead of the second question mark.

 

                        For more information check the javadoc for the java.sql package.

6.     Run the SQL statements.

7.     Close each of the SQL statements using the Statement.close() method.

8.     Close the connection to the iSeries database.

Example for accessing an iSeries database file

/*To use the following example:

1.     Make sure the jt400.jar is added to the classpath of your client.

2.     The collection you intend to use e.g. MYLIBRARY must exist on your target iSeries

*/

// 1- Import AS400JDBCDriver class contained in jt400.jar and the JDBC package.

import java.sql.*;

import java.util.Properties;

import com.ibm.as400.access.AS400JDBCDriver;

 

public class JDBCExample {

 

    public static void main(java.lang.String[] args)

    {

        String URL    = "jdbc:as400://iSeries_system_IP";        // Replace iSeries_system_IP by your own system name or IP address.

 

 

        Connection c = null;

        Statement s = null;

 

       

        try {

 

            // 2- Register the JDBC driver with the DriverManager

            DriverManager.registerDriver(new com.ibm.as400.access.AS400JDBCDriver());

            System.out.println("Successfully registered");

            // Create the connection properties

            Properties properties = new Properties ();

            properties.put ("user", "userid");                                // Replace user by your own user profile name.

            properties.put ("password", "password");                  // Replace password by your own password.

 

// 3- Connect to an iSeries database through the DriverManager.

            // Connect to the local iSeries 400 database

            c = DriverManager.getConnection(URL, properties);

            System.out.println("Connected to iSeries 400 database");

            // Create a Statement object.

            s = c.createStatement();

            // Delete the test table if it exists.  Note that

            // this example assumes throughout that the collection

            // MYLIBRARY exists on the system.

            try {

                s.executeUpdate("DROP TABLE MYLIBRARY.MYTABLE");

            } catch (SQLException e) {

            }

                // 4- Create the SQL statements that you want to use to update the data in your table.

            // Run the SQL statement that creates the table to be used in this example.

            s.executeUpdate("CREATE TABLE MYLIBRARY.MYTABLE (NAME VARCHAR(20) CCSID 420 NOT NULL WITH DEFAULT, ID INTEGER)");

                                    // Replace MYLIBRARY by the name of the library you are using to create your table.

                                    // Replace MYTABLE by the name of the table you are creating.

                                    // Replace CCSID 420 by CCSID 424 for Hebrew data.

 

 

        } catch (SQLException sqle) {

            System.out.println("Database processing has failed.");

            System.out.println("Reason: " + sqle.getMessage());

        } finally {

            // Close database resources

            try {

                if (s != null) {

                    s.close();

                }

            } catch (SQLException e) {

                System.out.println("Cleanup failed to close Statement.");

            }

        }

 

 

       

        PreparedStatement ps = null;

        //The following array may contain Bidi strings, English data is used as an example.

        String[] nameArray = {"DCBA", "Fred", "Mark", "Scott", "Jason",

                              "John", "Jessica", "Blair", "Erica", "Barb"};

        try {

 

            // Create a PreparedStatement object which will be used to insert data into the

            // table.

            ps = c.prepareStatement("INSERT INTO MYLIBRARY.MYTABLE (NAME, ID) VALUES (?, ?)");

 

        for (int i = 0; i < nameArray.length; i++) {

                //5- Set IN parameters and register OUT parameters for your PreparedStatement and CallableStatement SQL statements

 

                ps.setString(1, nameArray[i]);      // Set the Name from our array.

                ps.setInt(2, i+1);                  // Set the ID.

                //6- Run the SQL statements.

                ps.executeUpdate();

            }

 

        } catch (SQLException sqle) {

            System.out.println("Database processing has failed.");

            System.out.println("Reason: " + sqle.getMessage());

        } finally {

            //7- Close each of the SQL statements using the Statement.close() method.

 

            // Close database resources

            try {

                if (ps != null) {

                    ps.close();

                }

            } catch (SQLException e) {

                System.out.println("Cleanup failed to close Statement.");

            }

        }

 

        

        try {

 

            ps = c.prepareStatement("SELECT * FROM MYLIBRARY.MYTABLE ");

            // 6- Run an SQL query on the table.

            ResultSet rs = ps.executeQuery();

             // Display all the data in the table

            while (rs.next()) {

                System.out.println("Employee " + rs.getString(1) + " has ID " + rs.getInt(2));

            }

 

        } catch (SQLException sqle) {

            System.out.println("Database processing has failed.");

            System.out.println("Reason: " + sqle.getMessage());

        } finally {

         // 7- Close database resources

            try {

                if (ps != null) {

                    ps.close();

                }

            } catch (SQLException e) {

                System.out.println("Cleanup failed to close Statement.");

            }

                //8- Close the connection to the iSeries database.

            try {

                if (c != null) {

                    c.close();

                }

            } catch (SQLException e) {

                System.out.println("Cleanup failed to close Connection.");

            }

        }

    }

}

 

 

Migration guidelines

Recommendations for Bidi CCSID settings on the iSeries

Traditionally the AS/400 used visual presentation for all jobs, displayed data, and storage.

The CCSID used is 420 for Arabic and 424 for Hebrew for interactive display and printing jobs.

System values

System values are pieces of information that affect the operating environment in the entire system. A system value contains control information for the operation of certain parts of the system. Supported Linguistic and Cultural iSeries Values must be adapted for each country.

Arabic System values setting:

System value

Value

QCCSID Coded Character Set Identifier

420

QCHRID Graphic character set and code page

Character ID . . . : 235

Code page . . . . . : 420

QLANGID Language Identifier

ARA

QKBDTYPE Keyboard language character set

CLB for Arabic X

Or

CLE for Arabic X Euro Currency

QLOCALE Locale path name

*NONE  is the default setting

/QSYS.LIB/ contains all the IBM supplied locales

/QSYS.LIB/AR_AA.LOCALE is the Arabic locale

QSRTSEQ Sort sequence

*HEX is the default setting

Hebrew System values setting:

System value

Value

QCCSID Coded Character Set Identifier

424

QCHRID Graphic character set and code page

Character ID . . . : 941

Code page . . . . . : 424

QLANGID Language Identifier

HEB

QKBDTYPE Keyboard language character set

NCB for Hebrew

Or

NCE for Hebrew Euro Currency

QLOCALE Locale path name

*NONE  is the default setting

/QSYS.LIB/ contains all the IBM supplied locales

/QSYS.LIB/IW_IL.LOCALE is the Hebrew locale

QSRTSEQ Sort sequence

*HEX is the default setting

 

Which CCSID should I use?

 

Traditionally the CCSID used on the iSeries is 420 for Arabic or 424 for Hebrew.

Advantages:

1.     The data is stored in the same sequence as displayed and printed. Hence simple display and storage algorithm resulting in high performance.

Challenges:

1.     Sorting the visual data is not possible as all the search engines operate on the beginning of the buffer (leftmost location) or the logical order.

2.     Searching for the visual data is limited to the exact image of the text. If the character shape (for Arabic) is different, it is not possible to locate such a string; therefore searching for part of a word is difficult.

Recommendations:

1.     Use CCSID 420 for Arabic or 424 for Hebrew to present EBCDIC data on the 5250 screens and system printing. The interactive jobs and printing jobs CCSID is inherited from the User profile’s CCSID. The User profiles default CCSID setting is the system value *SYSVAL. The system value controlling the default-coded character set identifier CCSID of the iSeries is QCCSID.

2.     Legacy data is tagged with CCSID 420 for Arabic or 424 for Hebrew. The data confined to iSeries environment and generated by legacy applications are associated to CCSID 420 or 424. This is most appropriate to display and print the data properly in the iSeries traditional environment.

 

Use an implicit CCSID for storage to enable the search and sorting of the EBCDIC data.

Advantages:

1.     The data is stored in logical order, it is possible to sort and search for the Bidi data.

2.     Ease of data interchange with Unix and Windows platforms as data is stored in implicit format.

Challenges:

1.     The original visual data is not always recoverable from the implicit data.

2.     Needs reordering and shaping before presenting the data to the end user.

Recommendations:

1.     Use implicit CCSID for tagging data storage items on the file or field level, to ease search, sort and data interchange with Unix and Windows platforms.

2.     Do not use implicit CCSID to tag interactive jobs or as a system value QCCSID.

3.     You may consider tagging a communication job used for data interchange across different platforms.

Arabic specifics:

1.     <Lam-Alef> is stored on 2 cells in implicit format <Lam> and <Alef>. The Bidi layout engine expands the <Lam-Alef> characters consuming the blank spaces at the end of the Arabic text in Visual LTR display mode that is the beginning of the storage buffer associated to string type 4. Please make sure the number of blank spaces in the beginning of the visual buffer exceeds the number of <Lam-Alef> characters in the field.

2.     Arabic digits: In code pages designed to support visual representation like CP420, CP864 and CP1046, different sets of values are assigned to Arabic and Latin numeric digits. Implicit code pages like CP425, CP1089, and CP1256 assign a single set of values to store numeric digits. Arabic or Latin digits are displayed according to the numeric shaping associated to the interface setting. For example, in Windows 2000 Control Panel -> Regional Options-> Numbers -> “Digit substitution” parameter specifies either National, None or Context digits substitution is performed. Search for Arabic numeric digits stored in visual CCSID is not possible for queries issued from implicit code pages. To allow such a search, tag the data elements with CCSID associated to implicit string types.

 

Intended use of each CCSID.

CCSID 8612:

1.     Tag the file or field with CCSID 8612 if you are planning to exchange the data with a client application. The interfaces on the client application (controls or widgets) are in Left to right (LTR) mode.

2.     To convert Arabic numbers from CCSID 420 to Latin numbers to allow the search for Arabic numbers in queries generated from implicit code page.

CCSID 62224:

1.     Tag the file or field with CCSID 62224 if you are planning to exchange the data with a client application. The interfaces on the client application (controls or widgets) are in Right to Left (RTL) mode.

2.     To convert Arabic numbers from CCSID 420 to Latin numbers to allow the search for Arabic numbers in queries generated from implicit code page.

CCSID 425:

1.     Tag the file or field with CCSID 425 if you are planning to exchange the data with a client application. The interfaces on the client application (controls or widgets) are in Left to right (LTR) mode.

2.     To convert Arabic numbers from CCSID 420 to Latin numbers to allow the search for Arabic numbers in queries generated from implicit code page.

3.     Some server jobs, like the HTTP servers, use special characters like { } [ ] \ ^ ~ for internal control. Those characters are not represented in CP420. Tag such a server job running in EBCDIC environment with CCSID 425 to allow the conversion of Arabic data. Note that the special characters in CP425 are converted to CP420 <SUB> control character as they do not have an equivalent in CP420. It is recommended to convert only from CP420 to CP425 and not from CP425 to CP420 if the special characters are introduced to the text as they will be converted to <SUB>.

CCSID 62251:

1.     Tag the file or field with CCSID 62251 if you are planning to exchange the data with a client application. The interfaces on the client application (controls or widgets) are in Right to Left (RTL) mode.

2.     To convert Arabic numbers from CCSID 420 to Latin numbers to allow the search for Arabic numbers in queries generated from implicit code page.

3.     Some server jobs, like the HTTP servers, use special characters like { } [ ] \ ^ ~ for internal control. Those characters are not represented in CP420. Tag such a server job running in EBCDIC environment with CCSID 62251 to allow the conversion of Arabic data. Note that the special characters in CP425 are converted to CP420 <SUB> control character as they do not have an equivalent in CP420. It is recommended to convert only from CP420 to CP425 and not from CP425 to CP420 if the special characters are introduced to the text as they will be converted to <SUB>.

CCSID62211:

1.     Tag the file or field with CCSID 62211 if you are planning to exchange the data with a client application. The interfaces on the client application (controls or widgets) are in Left to right (LTR) mode.

CCSID 62235:

1.     Tag the file or field with CCSID 62224 if you are planning to exchange the data with a client application. The interfaces on the client application (controls or widgets) are in Right to Left (RTL) mode.