Solved

Can I have AS400 Program/Scripts Created to Copy LIBs for Data Refresh?

Posted on 2010-09-14
11
1,319 Views
Last Modified: 2013-12-06
We are on AS400 V5R4.

We are planning to do a data refresh. We use the JD Edwards EnterpriseOne software. I coudl try to do the refresh by running built in tools to kick off and run and perform the refresh for me. However, this process takes forever to run.

What I would like to do is have some AS400 scripts created to run and do the COPYLIB to refresh my LIBs and update a couple of tables for me.

The refresh will be copying our 3 Production LIBs (COPD7333,  PD7333DNT and PD7333) to our 3 development LIBs (CODV7333,  DV7333DNT and DV7333) and our 3 test LIBs (COPY7333,  PY7333DNT and PY7333)

I would like to break up the program script into two. So one script to copy the production libs to development libs. And another script to copy production libs to test libs.

After the copy has been completed, I need to have two different tables up dated. Two sets of tables for both development lib and test lib.

The first table AFTER the copy that needs to be updated is: F983051 which is in the PY7333DNT lib for test and in the DV7333DNT lib for development. After the script copies the production lib (PD7333DNT) to PY7333DNT (test lib) that F983051  has a field that will show “PD7333” for production. Well, after the copy that field will need to be updated to reflect the test environment (PY7333). And after the copy from production to development lib (DV7333DNT) that table will need to be updated for development.

So two tables will need to be updated: F983051/PY7333DNT & F983051/DV7333DNT
WHERE VRENHV = ‘PD7333’

The other table is the F9861 table. Similar to the F983051 table, after each refresh, this table will need to be updated. ALL the records in this table that refrence: PD7333 in the SIPATHCD field I want those records created for: DV7333 (for development) and PY7333 (for test).

So, in the testing refresh script, there should FIRST be steps to SAVE the existing LIBs: COPY7333,  PY7333DNT and PY7333

Then the refresh tasks to copy production LIBs to create or update the test LIBs above. Or however you recommend doing so on the AS400.

Then in that same testing script have commands to update the two tables mentioned.

And any other commands steps that should be included. Any data included to ensure data integrity?

Then I need to have another script setup the same way but for the “development” LIBs.

Does all that make sense?

Let me know if you need me to provide you with any other information.

Thanks!

9-14-2010-12-18-56-PM.jpg
9-14-2010-12-23-35-PM.jpg
9-14-2010-12-23-49-PM.jpg
9-14-2010-12-23-56-PM.jpg
9-14-2010-12-24-03-PM.jpg
0
Comment
Question by:matrix0511
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 33679279
Please can you tell me why you need a data refresh?
I'm working already with the as/400 since the begining, but I can't remember that I did any data refresh ever.

In the past I did some refresh actions on a unix server, but for the as/400, I can't imagine why you like to do that.

Please explain.

Regards,
Murph
0
 
LVL 34

Assisted Solution

by:Gary Patterson
Gary Patterson earned 200 total points
ID: 33680795
Use the cl commands savlib/rstlib to save and restore the libs. You can save to tape or if you have enough space, you can create a save file (crtsavf) and save to disk.

Once that is complete you can use a series of SQL UPDATE statements to update the files.

You can script this by putting the SQL statements into a source member and using the runsqlstm command to execute the SQL script.

- Gary Patterson
0
 

Author Comment

by:matrix0511
ID: 33682218
@murphey2, we user JD Edwards software. We have 3 different environments that users access. But right now Development and Test environments are not in synch with Production. we want the development & test environmnets (which have there own LIBs mentioned above) to look like production. so we have to do data refresh.

I know plenty of 400 clients who do refreshes all the time. It is nessary to do this often depending on your needs.

@Gary, can you give me the steps to put the backup commands in a script? I've never done that before. Thanks.
0
 
LVL 34

Assisted Solution

by:Gary Patterson
Gary Patterson earned 200 total points
ID: 33682752
Here's a basic tutorial on using PDM to create a CL program:

http://www.texas400.com/tutpdm.html

Follow the same basic process (without compiling) to create a source member containing the SQL statements that you want to run using RUNSQLSTM.

http://www.mcpressonline.com/programming/sql/run-sql-from-a-source-member-using-runsqlstm.html

If you aren't familiar with CL programming, it is too big a topic to cover here.  I suggest you take a class (Manta Technologies has good online classes), or buy a good CL programming book, like one of these:

http://www.amazon.com/Control-Language-Programming-400-2nd/dp/1882419766
http://www.amazon.com/Complete-CL-Ted-Holt/dp/1583470905/ref=pd_sim_b_7

I've used this one as a textbook for a CL Programming class that I used to teach:

http://www.amazon.com/CL-Programming-IBM-AS-400/dp/0962182524/ref=sr_1_3?s=books&ie=UTF8&qid=1284561348&sr=1-3

- Gary Patterson


0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 33682802
Hi matrix0511,

Oke, replacing the test-data :-)
refresh a DB in other DB's (like progress etc.) is
  - a dump of all data in ascii files
  - rebuild all files
  - reload all data
  - build indexes
(only because they don't know a RGZPFM :-)

I was affraid you liked to do something like that

Good Luck,
Murph
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:matrix0511
ID: 33684557
oK. I was hoping someone would already have a script that I could just modify for our refresh. This refresh is due to start in just a week or so. Not enough time to read up on all the steps. so I was just hoping someone could provide a template, something I could build on.

since the steps are prettys striaghtforward I thought this would not take that long to prepare.
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 33684729
Hardest part of the task is figuring out how to use PDM and SEU to edit and compile the CL.  If it is time-critical, and you don't have time for the learning curve, then it is probably time to hire a consultant.

The basic CL is:

PGM
CRTSAVF ...
SAVLIB ...
RSTLIB ...
RSTLIB ...
RSTLIB ...
RUNSQLSTM ...
DLTF (your save file)
ENDPGM

the RUNSQLSTM scrips is just a series of SQL UPDATE statements.

Just prompt each CL command and fill in the correct parameters.  If you have trouble, post your CL and the SQL script and any error message and we can help.

- Gary Patterson
0
 
LVL 27

Expert Comment

by:tliotta
ID: 33685605
...I thought this would not take that long to prepare.

It shouldn't take long to prepare at all... unless there are referential constraints or triggers or stored procedures or files with specific multiple members or any number of other details about which we might have no clue.

A "data refresh" into a different schema (library) on the same system is full of pitfalls.

If all is well, you might get by with a trivial list of CPYF commands, simply copying data from tables in one library to the corresponding tables in a second and third library.

But without knowing schema requirements, a "script" can be tricky.

Gary's "save/restore" where you save the source library and restore into a different library name might be as good as it's likely to get; but even that can be tricky if there are thing in DEV or TEST that you don't want disturbed.

Tom
0
 
LVL 3

Accepted Solution

by:
ScottParker earned 300 total points
ID: 33793184
We use JDE also.   We just take one of our Monthly system saves, and restore certian libs back.
I.e.  

Our production libs are   GRTDTA, and GRTCOM
after the system save, we restore GRTDTA to GRTDTAC1  and GRTCOM to GRTCOMC1
0
 

Author Comment

by:matrix0511
ID: 33806746
Ok. Thanks Scott.
0
 

Author Closing Comment

by:matrix0511
ID: 33806759
Thanks!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Applying quotas on system paritions 4 73
ODBC Informix Driver 11 114
Storing REST data JSON to an iSeries (as400) IFS file 12 88
User cannot delete a folder from the desktop. 3 60
Many people tend to confuse the function of a virus with the one of adware, this misunderstanding of the basic of what each software is and how it operates causes users and organizations to take the wrong security measures that would protect them ag…
I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now