IBM i SQL numeric to alphameric

So – today I was talking to a colleague who wanted to make a database change to a big old file. This meant changing one of the fields from 7 numeric to 12 alpha. Sounds simple right? Oh if only life were that simple…

Now add the complexity that you have to do this change in Turnover and populate the data from the old file into the new file, at the time the promotion form is run. Obviously, we want to retain any existing data in the file while converting an existing numeric data in that field into the same place but as alphanumeric. Now it’s a little trickier.

Why?

Turnover uses CPYF to load the file and CPYF promptly poops the bed because of the difference in data types between the two files. When Turnover compiles the new physical file it wants to copy the data from the old one into the new one. But because of the datatype changes (numeric layout to character layout) both our CPYF options FMTOPT(*NOCHK) and FMTOPT(*MAP *DROP) fail drastically. This is not nice. It makes me cry into my beer.

So, lets make a little dummy file and do some experimenting.

Create a file with a numeric in it:

 FIELD 10 
 TRICKY 7,0 which needs to change to 12A
 SOME 10 
 MORE 10

So, here is some DDS for them both:

 A  R TEST
 A    FIELD      10A
 A    TRICKY     7P00
 A    SOME     10A
 A    MORE     10A

and PFALPHA:

 A R TEST 
 A   FIELD   10A 
 A   TRICKY  12A         COLHDG('ALPHA NOW') 
 A   SOME    10A 
 A   MORE    10A

I used DFU to stuff in some dummy data:

 Line ....+....1....+....2....+....3....+....4....+
        FIELD TRICKY SOME MORE 
 000001 a     1      a    a 
 000002 b     2      b    b 
 000003 c     3      c    c 
 000004 d     4      d    d 
 000005 e     5      e    e 
 000006 fff   999    flip flop

and now its time to try and copy the data from the old numeric file to the new alpha numeric one

STEP1 – CPYF with *MAP *DROP is a *FAIL

FMTOPT(*MAP *DROP) –  *bombs* because it;’s unable to map the numeric to alpha

 CPYF FROMFILE(PFDEC) TOFILE(PFALPHA) MBROPT(*REPLACE) FMTOPT(*MAP *DROP)
  Field TRICKY will not map from file PFDEC in LITTENN.
  FMTOPT(*NOCHK) required to copy file.
  Copy command ended because of error.

Well that sucks. So lets try

STEP3 – CPYF with *NOCHK

FMTOPT(*NOCHK) which copies the records OK but creates corrupted fake-numeric data in the file.

 CPYF FROMFILE(PFDEC) TOFILE(PFALPHA) MBROPT(*REPLACE) FMTOPT(*nochk)
  Buffer length longer than record for member PFDEC.
  6 records copied from member PFDEC.

as you can see this copies quite happily but the data is double mc-screwy like this:

 Line ....+....1....+....2....+....3....+....4....+
  FIELD ALPHA NOW SOME MORE 
 000001 a ■a a 
 000002 b 
 000003 c ■c c 
 000004 d |d d 
 000005 e ¬e e 
 000006 fff r¤flip flop

Luckily help is at hand with our old friend SQL 🙂

SQL comes to save the day

Use SQL to Copy from Numeric fields mapping direct into Alphanumeric character fields

 INSERT INTO LITTENN/PFALPHA SELECT * FROM PFDEC
  6 rows inserted in PFALPHA in LITTENN.

and it looks like it worked….

 Line ....+....1....+....2....+....3....+....4....+
        FIELD TRICKY SOME MORE 
 000001 a     1      a    a 
 000002 b     2      b    b 
 000003 c     3      c    c 
 000004 d     4      d    d 
 000005 e     5      e    e 
 000006 fff   999    flip flop

So, in Turnover we now have a straight forward way of promoting and converting the data.

We can use the PRERUN command to take a backup of the file, promote the file with NO DATA and then use POSTRUN command to add this SQL statement to re-populate the file for us 🙂

Groovy.