Hello all - any help with this would be appreciated.
I searched the forum, and did not see any threads that addressed this issue.
We have a data field (numeric that today is stored as N7.1) and it is being reconciled to another system of record DB2, that has 2 significant decimal digits. Because of this, much manual adjusting has to be done. My task is to change the Data field to N7.2, and all program references to that field need to be changed to reflect the 2 digits. I can handle the 200 some program changes, but the DB Copy and convert has me wondering.
2 issues
One is:
Can anyone suggest a best practice way to change the DB? Is there any utility within Natural/Adabas that can convert the DB, or will a program need to be coded to change N7.1 to an N7.2 field.
Second:
Say the DB is copied to Dev and the DDM is changed to N7.2, Is it possible to write a prog that will update the field by reading every record and then multiplying it by 10 and saving it? or is there another way? :idea:
A quick observation. If these are old programs in Report Mode, or if you are in Mixed Mode, the DDM does not have to agree with the FDT. Thus your job would be a lot simpler.
Question. What do you mean by "We have a data field (numeric that today is stored as N7.1) and it is being reconciled to another system of record DB2, that has 2 significant decimal digits. "
Surely, you do not mean checking to see if they are the same since the Adabas record has only one position after the decimal place.
Are you converting from an Adabas file to a DB2 file? Is the Adabas file going to go away? Or, are they to both exist (which is why you need the N7.2 on the Adabas file).
Is the DB2 file to dictate values to the Adabas file? Are the files in synch except for the extra digit for this field? If so, you could delete the Adabas file, change the DDM, and totally rebuild the Adabas file from the DB2 file.
steve
If your Adabas file is staying in place and you need to have N7.2 values in place of your N7.1 field, the best way I know is to add this N7.2 field as a new field with a new name, do the conversion by reading all the records and moving the N7.1 variable to an N7.2 variable (keeping the value but changing the internal data), and then renaming the new variable to the old name and dropping the old one from the DDM.
It’s important you have exclusive use of this file during the conversion as you don’t want other users modifying the old value after you have converted that record. If this isn’t possible, you will have more work to do to ensure your new field is kept in sync with old field changes.
I assume this is necessary because the hundredths decimal position could have a non-zero amount… otherwise why do it?
I would suggest a filemod to change the database field size to 9 digits (=N7.2) and update the DDM to make the field N7.2. Since decimal places are not known by Adabas, you will need to multiply the existing values by 10 with a program, as Brian suggests, in order to see the former values.
I think there is more danger in adding a new field to hold the new size than in just using a filemod to increase the size of the field. If any existing programs are missed that are pointing to the original field, they won’t see the new field - you will need to ensure that a CATALL is done and successful. And that you have validated all other accesses to the file (non-Natural programs - Adabas SQL Gateway?) are also modified to find the new field in place of the old.
However, if as Steve suggests, you are populating the data from DB2, then perhaps have a program update the values from DB2, which will ensure that they are in sync.
Some possible negative outcomes if you multiply in place are that you will have some records updated, some not during your conversion. You have to be absolutely sure in the case of an abend that you restart exactly where you left off. Writing ISN to DDPRINT is no good if you actually BT’d any of your updates.
Also be sure you don’t re-read any records you already updated, as could happen if you read physical and the increase by 1 byte causes a block split. Be sure also you don’t miss any records for the same reason.
I like the new field because you can start all over as many times as you need to, the existence of a value proves you did the conversion once, and you can see if the record has been converted or not.
Programmers who don’t understand the nuances of the database may not understand why their code didn’t work as they thought. I like building restartability and recoverability into any conversion plan. While it’s possible to code this to avoid the problems I mentioned above, I would rather not trust them to do so. As the DBA, I would propose the new field and have them populate it and let me know when done so I can change the DDM.
Imagine if this was the invoice detail file and they were converting a money field and it failed in the middle with no idea what values were multiplied by 10 and which weren’t. EEKS!!!
We (Douglas, Brian & I) are spinning our wheels without needed information.
As noted in my first post, if you are not in Structured Mode, this may be all but trivial. It is perfectly legal in both Adabas and Natural to store an N7.2 value in a field defined to Adabas as N8. Just change the format in a View: e.g. 2 field (n7.2) .
Assuming you are not in Structured Mode, Natural passes the nine digits to Adabas, which stores the nine digit value. If you read the record with N7.2, all is okay. If you read the record with N7.1, it might not be so good.
BUT, if this is a one time thing, this may be perfectly okay.
So, we need information as to what is required. Is the DB2 record to populate the Adabas file with N7.2 values? If this is correct, will the Natural code continue to be used? Will the Adabas file be used by other programs than the ones you are going to change? If not, and if the programs are not Structured Mode, the changes may be all but trivial, with no changes to Adabas, or the Adabas file, just overrides to views of the file, and if you happen to have them in a single external data area, this could be a five minute fix.
Of course we are all familiar with the law of nature that says such changes are never such that they are trivial.
steve
Thanks to all 3 of you for your input. I thought that I replied to this yesterday, however it did not post. not sure why. Here’s some feedback regarding why this is needed.
The field I am talking about is a length field (footage N7.1). The DB2 system is not our system(external to our company), but rather data of footage that is given to us as 7.2. To be more specific, they are totalled, and our footage is totalled. For years the two could never reconcile due to the difference in significant digits between the 2 systems. Manual intervention has been needed to assure accountability. This is something that has been on a wish list for some time.
After looking at your potential solutions, we now have several options to choose from, which was the purpose of my post.
I spoke with the user and they assured me that there is never to be an individual item that is a million feet long, so we probably are leaning toward the solution that will just change the DDM to 6.2 and then do the 10 times update. This system is not a high volume system so making sure no updates are occurring during the change should be fairly easy.
I’m not sure which one we will go with, but I assure you your input is greatly appreciated. I will let you know how things go as we progress.
Thanks
Bill
A filemod is fairly trivial change if the field is null or normal compression (that is, not fixed length) - it just changes the standard length of the field in the FDT from 8 to 9 digits and you can do it from Predict so both the DDM and the FDT match.
Nonetheless, it is easy enough for you to include a check on the data values while doing the 10-times update to make sure there really aren’t any values that might overflow the 999,999 feet value.
I was talking about the field, a length field (video N7.1). DB2 system is not our system (outside of our company), but to give 7.2 gives us the picture data. To be more specific, they are total, and our is total. Over the years.