OEPRCFIL Rebuild

[jmccall]

I'm in the middle of a price increase project that requires a lot of work on a very large OE price file and I'm concerned that at some point it may be necessary to rebuild or export/initialize/import the OEPRCFIL.

This is a MS-SQL installation. The OEPRCFIL has just under 2 million records. I've attempted to benchmark the rebuild process in the event I need to do it and after eight hours I aborted the rebuild and was only about one third through the file.

The reason I'm concerned about the possibility that a rebuild may be required is that when attempting to add records via Macola after extensive work on a table I sometimes get errors indicating a problem with the A4GLIdentity field and I usually fix those by rebuilding the table. But this table is unusually large.

Is there a way to accomplish the same result via SQL that Macola does via it's rebuild or export/initialize/import process.

Any suggestions would be most welcome.

 

[dgillz]

How about using a SQL delete query of these records if they are expired? Say thru 12/31/2004?



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[jmccall]

None of the records are expired. And all are actively in use to cover pricing agreements with specific customers. There is nothing I can do to reduce the number of records in the table. The posture of the sales department is such that any customer (2000+) customers can negotiate any price on any item (4000+) which could potentially result in as many as 8 million special pricing records.
My problem/concern is that if I ever need to rebuild this table and need to use Macola to do so it may be out of service for an extremly long period of time. If I could use MS-SQL for the rebuild it may be able to be done in a much more reasonable time frame. I am unclear as to how to properly handle the A4GLIdentity field to avoid having Macola error when I try to add additional records via Macola after building/rebuilding the table in SQL.

 

[dgillz]

Please explain the concern about the A4GLIdentity field. When you say you are unsure how to "properly handle" this field, what do you mean?

Also, I am assumming you are not keying these records in by hand. How are you importing them? In terms of "properly handling" the A4GLIdentity field when you import records into this table, the answer is you do nothing to this field, SQL will handle it automatically as a counter or autonumber field.

I just looked at the table structure of the OEPRCFIL_SQL table in Enterprise Manager and the A4GLIdentity field is numeric and length = 5. This means you cannot have more that 99,999 records in the table. How are you getting 2 million records in here? Have you modified the table?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[jmccall]

My think my concern stems from having incorrectly populated that field in the past and then having a problem when trying to add records from Macola to the table.
Your comment about the field type and length got me to look at the table design and that may have answered my own question. The data type shows as numeric and the length shows 5 however the precision value shows 9. The data in the A4GLIdentity of my table shows an actual max value of 11354447 and a min value of 9534088. So apparently the length of 5 does not restrict the table size to 99,999 records.
However I also noticed the three Identity attributes that control the autonumbering.
I'm going to try making a copy of the original table then use Macola to initialize another OEPRCFIL_SQL table then do a SQL insert into the new table from the original table all fields except A4GLidentity. That should allow the A4GL field to populate itself and keep me out of the trouble I have when I incorrectly populate the field.

I'll let you know if that works for me.

Thanks for the help.

 

[kscheeler]

You're right, you should not populate the A4glidentity field because it's an identity field and is set up to autonumber. As you saw, the internal rebuild function will be way too slow with that number of records. A SQL delete and insert will work much quicker. BCP may be even faster as well.


Kevin Scheeler

 

[jmccall]

Reinitializing the table in Macola and then doing a SQL insert to populate the table while letting the A4GLIdentity field populate itself did the trick.
My 1,860,320 records inserted in less than 15 minutes and no problems with the A4GLIdentity field. I was able to copy that OEPRCFIL_SQL table from a pilot/testing company into my live company and work with it successfully.
Thanks for helping me find the answer.