Copying tables from PY to DV

[mhoyt]

We wish to copy all tables defined by a SQL query from a JDE_CRP to a JDE_DEVELEOPMENT SQL Server 7 database. A script was written to do this using a cursor that passed table owner from (CRPDTA), table owner to (TESTDTA) and table name to a stored procedure.

The stored procedure truncated the table and then used INSERT INTO TESTDTA,tblname select * from CRPDTA.tblname

This works fine but takes about a hundred years to run. It seems to be logging each record inserted even when truncate log om checkpoint is set.

Using DROP TABLE, SELECT INTO ... is much faster but obviously kills indexes, security, etc...

Using a database backup and then restore into new database is faster but we lose views, security, etc ...

We are looking for a way to quickly copy the tables into EXISTING TABLES with EXISTING SECURITY, etc... This should not affect any tables, views, procedures, etc... not specifically named in the script.

Thinking now we may have to use BCP and BULK insert but that is just plain ugly.

Any help would be appreciated.

Thanks

Mike Hoyt
Database Analyst
IMP Group International

 

[cbruce]

One option is to use R98403.

R98403 is the way the E1 upgrade/install process creates and populates E1 databases. There are options with this UBE--and as you can imagine it is a dangerous UBE if you do not set the processing options correctly.

Here is what you can try:

1. On an E1 workstation, sign in and run batch versions (BV)
2. Create a new version of R98403 (call it XPYTODV)
3. On the processing options for XPY2DV do the following:

"Environment" tab:

Edit box "1." leave blank
Edit box "2." use visual assist to pick data source for PY
Edit box "3." enter 1 (more on this later)
Edit box "4." use visual assist to pick data source for DV
Edit box "5." leave blank

"Update" tab:

Edit box "6." Enter 0 (when you are ready to run for real, enter a 1 in this field)
Edit box "7." Enter 'A' (this will re-create the table and indexes)
Edit box "8." Enter '1'
Edit box "9." Leave blank

Leave the remaining edit boxes blank.

Click OK to save the processing options.

Before running R98403, be sure you backed up your database.

For your XPYTODV version, go to the "Tools" tab and press "Set Selection". Here is where you specify which tables you want to re-create. The dataselection is over the F9860. You can provide a list of values by object name or system code, etc. Once you have set the data selection save.

Now you are ready to run R98403. Remember that on the "Update" tab of the processing options, we set the 6. option to "0" which means that the UBE will print out all the tables in your data selection to a pdf without actually doing anything to the database (i.e. in "Prooof Mode" the database is not modified.)

Once you are satisfied that the tables in the pdf are indeed the tables you want to drop and re-create (with the indexes), then change option 6. to a "1". This puts the version into "Final mode". Make sure from your PDF that your source and destination datasources are correct.

Once you are ready, then run R98403 for real. The way you have set up the PO, R98403 will re-create only the tables that you specified in you data selection.

You can then use your SQL scripts to do INSERT INTOs into these newly created tables.

R98403 can also copy the data frpm PY to DV if you want it to. Just set Edit box "3." to a 2. Then when you run R98403, it will drop and recreate the desired tables and populate from PY to DV. Of course, the copy will take longer than if you do an INSERT INTO. But one advantage is that the data selection for the R98403 (i.e. what determines what tables it will copy), will be saved with your R98403 version. You can, for instance, create a version for each set of tables that you want to copy.

Anyway, I hope that was informative. Again, always take care when running R98403. Backup and proceed cautiously.

Cheers,

Chris

 

[jberry]

Our Central Objects are on SQL and we are able to refresh the central objects by backing up the SQL database and restore it, then running a SQL statement to update the owner (takes about 5 seconds to run), then run the program to synch up the Object Librarian.

If you need this document, I can send it to you. Even though the Central objects are on SQL, we are running a AS/400 platform, so you may be to determine if it will work for you. We use it to refresh our central objects from one environment to another and it works great.

Thanks,
Jimmy Berry
AS400
JDE ERP 8.0 SP23

 

[brother_of_karamazov]

DTS package?

 

[jdesmm]

Mike,

We use the database backup & restore approach. With a little bit of scripting, it's not difficult to update your security. As to non-table objects like views and procedures, are these objects really specific to the DEV environment only, or are they in all your environments, just with different ownership? If it's an issue of ownership and security, that is again easily scripted. We make it a practice to build custom views and procedures in a separate database from JDE databases so that they are not affected in the refresh process, so I would recommend you consider that option if the objects are indeed specific to DEV. If you're interested in the way we have scripted our refresh, send me a private message.