IBM Support

Converting Packed Fields to Zoned or Zoned Fields to Packed Using Query/400

Troubleshooting


Problem

This document shows an example of using Query/400 to change a packed field to a zoned field and changing a zoned field to a packed field.

Resolving The Problem

There is no documented, precise procedure in the Query for iSeries Use manual that defines if arithmetic output from the Define Result Fields will be in packed or zoned format. In most cases, if the length and decimal for arithmetic output is defined, it will become a zoned field when output to a database file. Conversely, if the length and decimal for arithmetic output are not defined, it will become a packed field when output to a database file.

Following is an example of how to use Query/400 to change from a packed field to a zoned field and from a zoned field to packed field. The file being used has two fields: P_NUM and Z_NUM. P_NUM is packed with length of 2,0; Z_NUM is zoned with a length of 2,0.

Query/400 definition:

Define Result Fields
Field       Expression                         Column Heading        Len   Dec
NEW_P_NUM1  P_NUM + 0                          No Length Specified            
                                                                             
                                                                             
                                                                             
NEW_P_NUM2  P_NUM + 0                          Length Specified        2     0
                                                                             

                                                                             
NEW_Z_NUM1  Z_NUM + 0                          No Length Specified            
                                                                             
                                                                             
                                                                             
NEW_Z_NUM2  Z_NUM + 0                          Length Specified        2     0


Following is a portion of the Display File Description (DSPFFD) for the file created from this query:

Field Level Information                                                  
            Data        Field  Buffer    Buffer        Field    Column  
 Field      Type       Length  Length  Position        Usage    Heading                
 P_NUM      PACKED       2  0       2         1        Both     P_NUM                  
 Z_NUM      ZONED        2  0       2         3        Both     Z_NUM                  
 NEW_P_NUM1 PACKED       3  0       2         5        Both     No Length Specified    
   Field text  . . . . . . . . . . . . . . . :  P_NUM + 0                              
 NEW_P_NUM2 ZONED        2  0       2         7        Both     Length Specified        
   Field text  . . . . . . . . . . . . . . . :  P_NUM + 0                              
 NEW_Z_NUM1 PACKED       3  0       2         9        Both     No Length Specified    
   Field text  . . . . . . . . . . . . . . . :  Z_NUM + 0                              
 NEW_Z_NUM2 ZONED        2  0       2        11        Both     Length Specified        
   Field text  . . . . . . . . . . . . . . . :  Z_NUM + 0  
                                                                             
                                                                             
In the above query, it did not matter if the field started out as packed or zoned. When the length and decimal are specified for the new field, the output field will be zoned. If the length and decimal are not specified for the new field, the output will be packed. Because this applies only to arithmetic expressions, a zero was added to each field to convert to the proper data type.

If a zero is not added, the results are slightly different. Below is the DSPFFD of the file after a query where no addition of 0 was done:

Field Level Information                                                  
            Data        Field  Buffer    Buffer        Field    Column  
 Field      Type       Length  Length  Position        Usage    Heading            
 P_NUM      PACKED       2  0       2         1        Both     P_NUM              
 Z_NUM      ZONED        2  0       2         3        Both     Z_NUM              
 NEW_P_NUM1 PACKED       2  0       2         5        Both     No Length Specified
   Field text  . . . . . . . . . . . . . . . :  P_NUM                              
 NEW_P_NUM2 ZONED        2  0       2         7        Both     Length Specified    
   Field text  . . . . . . . . . . . . . . . :  P_NUM                              
 NEW_Z_NUM1 ZONED        2  0       2         9        Both     No Length Specified
   Field text  . . . . . . . . . . . . . . . :  Z_NUM                              
 NEW_Z_NUM2 ZONED        2  0       2        11        Both     Length Specified    
   Field text  . . . . . . . . . . . . . . . :  Z_NUM

In this case, not adding the zero affected the zoned filed, Z_NUM. It did not become packed when the Length and Decimal were not specified.

[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU009","label":"Systems - Cognitive"},"Component":"DB2 for IBM i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":""},{"Product":{"code":"SSC5L9","label":"IBM i 7.2"},"Business Unit":{"code":"BU009","label":"Systems - Server"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":""},{"Product":{"code":"SSC52E","label":"IBM i 7.1"},"Business Unit":{"code":"BU009","label":"Systems - Server"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":""},{"Product":{"code":"SSC3X7","label":"IBM i 6.1"},"Business Unit":{"code":"BU009","label":"Systems - Server"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":""},{"Product":{"code":"SSTS2D","label":"IBM i 7.3"},"Business Unit":{"code":"BU009","label":"Systems - Server"},"Component":" ","Platform":[{"code":"","label":null}],"Version":"","Edition":""}]

Historical Number

8054651

Document Information

Modified date:
18 December 2019

UID

nas8N1010222