Topic
  • 9 replies
  • Latest Post - ‏2013-03-06T02:21:34Z by scott_klement
SystemAdmin
SystemAdmin
535 Posts

Pinned topic Consolidating IBM i

‏2013-03-05T17:46:14Z |
Hello, We have multiple IBM i regionvise and we are planning to consolidate them into a single LPAR. We are planning on converting our database to Unicode. What can be a better approach , using RPG program to convert data into Unicode or convert it externally (using ETL ) and load them into Db for i?
Updated on 2013-03-06T02:21:34Z at 2013-03-06T02:21:34Z by scott_klement
  • JonParis
    JonParis
    115 Posts

    Re: Consolidating IBM i

    ‏2013-03-05T18:15:09Z  
    Can you not simply create the new tables with the required column definitions and just copy the tables? I see no need for an external conversion.

    What do you plan to do with all of your existing programs? Switching like this requires quite a few changes.

    You might look to tools like those from Databorough or Xcase to help make the transition.
  • SystemAdmin
    SystemAdmin
    535 Posts

    Re: Consolidating IBM i

    ‏2013-03-05T19:06:05Z  
    • JonParis
    • ‏2013-03-05T18:15:09Z
    Can you not simply create the new tables with the required column definitions and just copy the tables? I see no need for an external conversion.

    What do you plan to do with all of your existing programs? Switching like this requires quite a few changes.

    You might look to tools like those from Databorough or Xcase to help make the transition.
    Jon, We were planning on converting the database into Unicode. We were not planning on changing the code. Chinese user will have his code page and will key in data and the program will convert it into unicode , same for European users they will have their code page. LEt me know if i am missing anything here.
  • JonParis
    JonParis
    115 Posts

    Re: Consolidating IBM i

    ‏2013-03-05T19:33:48Z  
    Jon, We were planning on converting the database into Unicode. We were not planning on changing the code. Chinese user will have his code page and will key in data and the program will convert it into unicode , same for European users they will have their code page. LEt me know if i am missing anything here.
    Are you saying that all data is already double byte? If so probably not a problem. But if you are currently set up for single byte operation then a Unicode change is going to have a very significant effect on your code.
  • SystemAdmin
    SystemAdmin
    535 Posts

    Re: Consolidating IBM i

    ‏2013-03-05T20:00:47Z  
    • JonParis
    • ‏2013-03-05T19:33:48Z
    Are you saying that all data is already double byte? If so probably not a problem. But if you are currently set up for single byte operation then a Unicode change is going to have a very significant effect on your code.
    Jon, What kind of issue are you foreseeing? We have one IBM i here in US (single byte) and other one in China (double byte) and were planning on consolidating them onto a new IBM I changing the database to unicode. What kind of problems we are missing here with the programs?
    Please enlighten me.
  • JonParis
    JonParis
    115 Posts

    Re: Consolidating IBM i

    ‏2013-03-05T21:07:33Z  
    Jon, What kind of issue are you foreseeing? We have one IBM i here in US (single byte) and other one in China (double byte) and were planning on consolidating them onto a new IBM I changing the database to unicode. What kind of problems we are missing here with the programs?
    Please enlighten me.
    Well at the simplest level every Data Structure that exists in the SBCS version of the programs will get bigger. That has the potential to screw up all sorts of things.

    See http://brucevining.com/images/stories/Presentations/WhatsWithTheseEBCDICASCIIandUnicodeCCSIDs.pdf for more information and various information resources.

    I highly recommend consulting Bruce on any internationalization project (which this effectively is). You can also learn a lot from this article: "More on Unicode and Converting RPG Applications"
    http://www.iprodeveloper.com/article/rpg-programming/more-on-unicode-and-converting-rpg-applications-64060

    The RPG compiler support gets better with each release so be sure to check the capabilities of the release level you are using.
  • barbara_morris
    barbara_morris
    385 Posts

    Re: Consolidating IBM i

    ‏2013-03-05T21:54:55Z  
    For converting the data to Unicode, I think you can use either ALTER FIELD, CHGPFM or CPYF. You don't need to use an RPG program to do the transformation.

    I am assuming that you will change to use UCS-2 (CCSID 13488) or UTF-16 (CCSID 1200). These would be the UCS-2 data type in RPG.

    After you have changed the types of your database fields, then you will probably have some changes for the RPG code. But I don't think the process will be particularly different whether you are starting from the DBCS or the single-byte version.

    I'm assuming you are using ILE RPG here.

    You will probably have to change the data types of your work fields to be UCS-2. If you used LIKE to define your work fields, where the like-field was a field in your database, then you won't need to do much.

    If you don't change the data types of your work fields, your RPG programs might compile successfully, because RPG will do implicit conversions for assignment and comparisons between A, G, and C.

    But your program might not work the way you want, since conversions from UCS-2 to DBCS or to single byte can lose data if there is a character in the UCS-2 data that doesn't have a match in the single-byte data.

    The new CCSIDCVT H spec keyword (added with PTFs for 6.1 and 7.1) can help you here. If you code CCSIDCVT(*LIST), you can get a section in the listing that lists every CCSID conversion in the module, and then you can use that information to help locate the work fields that need to be changed to UCS-2.

    If you code CCSIDCVT(*EXCP), you will get an exception at runtime if a conversion causes data loss.

    Here's some more information about the CCSIDCVT keyword: http://ibm.biz/BdxXWJ
  • SystemAdmin
    SystemAdmin
    535 Posts

    Re: Consolidating IBM i

    ‏2013-03-05T22:30:21Z  
    For converting the data to Unicode, I think you can use either ALTER FIELD, CHGPFM or CPYF. You don't need to use an RPG program to do the transformation.

    I am assuming that you will change to use UCS-2 (CCSID 13488) or UTF-16 (CCSID 1200). These would be the UCS-2 data type in RPG.

    After you have changed the types of your database fields, then you will probably have some changes for the RPG code. But I don't think the process will be particularly different whether you are starting from the DBCS or the single-byte version.

    I'm assuming you are using ILE RPG here.

    You will probably have to change the data types of your work fields to be UCS-2. If you used LIKE to define your work fields, where the like-field was a field in your database, then you won't need to do much.

    If you don't change the data types of your work fields, your RPG programs might compile successfully, because RPG will do implicit conversions for assignment and comparisons between A, G, and C.

    But your program might not work the way you want, since conversions from UCS-2 to DBCS or to single byte can lose data if there is a character in the UCS-2 data that doesn't have a match in the single-byte data.

    The new CCSIDCVT H spec keyword (added with PTFs for 6.1 and 7.1) can help you here. If you code CCSIDCVT(*LIST), you can get a section in the listing that lists every CCSID conversion in the module, and then you can use that information to help locate the work fields that need to be changed to UCS-2.

    If you code CCSIDCVT(*EXCP), you will get an exception at runtime if a conversion causes data loss.

    Here's some more information about the CCSIDCVT keyword: http://ibm.biz/BdxXWJ
    Thanks Barbara, Your explanation has helped a lot.

    Since UCS consumes twice the space as that of single byte , will this cause an issue with the Screen fields , report fields and their position on the screen?
  • barbara_morris
    barbara_morris
    385 Posts

    Re: Consolidating IBM i

    ‏2013-03-05T22:38:56Z  
    Thanks Barbara, Your explanation has helped a lot.

    Since UCS consumes twice the space as that of single byte , will this cause an issue with the Screen fields , report fields and their position on the screen?
    For screens, you can use the second parameter of the CCSID keyword to control the amount of space for the field on the screen. In this example, MYFIELD was formerly 10A, and by coding *MIN, the UCS-2 field occupies the same amount of space on the screen.
    
    A            MYFIELD       10G  B  3  4CCSID(1200 *MIN)
    


    That wouldn't work for a DBCS job though, since you would need the wider fields. Maybe you would need separate display files for your DBCS jobs.

    I have not experimented with printer files, but it looks like the CCSID keyword for printer files also has additional parameters to control the actual output.
  • scott_klement
    scott_klement
    244 Posts

    Re: Consolidating IBM i

    ‏2013-03-06T02:21:34Z  
    I would add that it's possible to create your PF in Unicode, and then build logical files that translate the data to/from ebcdic as it's read from the PF. The advantage here is that you can change your RPG programs slowly without as much disruption.

    On 5250, Chinese characters may be so large that they take up the space of two western characters. So that might be something to consider.

    Though, in modern display interfaces (like web) that's not true, and Chinese characters take up one position just as western chars do. So you might consider moving to a modern interface.