• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

SQL 2008R2 and JDE table partitioning

cncjay

Well Known Member
Hello all,

We are very interested in learning about table partitioning some of our largest (example: F0911 (400+Million records), along with various other larger tables. I've seen this topic discussed but didn't really find out if anyone actually partitioned successfully.

The 3rd party tools such as ARCTools and PurgeIT, both companies have the associated tables relationships figured out. Is that what is required to do the SQL table partitioning to work with JDE?

If you have done this successfully, kindly post your feedback.

Regards,
CNCJay
 

altquark

Legendary Poster
CNCJay - what platform is your database on ? This is an important piece of information that helps understand table partitioning.
 

cncjay

Well Known Member
Hi Jon,

Yes, indeed. We are on Microsoft SQL2008 R2 - enterprise edition. JDE is 9.0 TR 8.98.4.10.

Regards,
jay
 

altquark

Legendary Poster
Table partitioning is done at the database level. Your best starting whitepaper to look into paritioning should be :

https://technet.microsoft.com/en-us/library/dd578580(v=sql.100).aspx

But - please remember that Oracle Document ID 1529603.1 states "Oracle has not tested nor certified Microsoft SQL Server database partitioning with JD Edwards EnterpriseOne. Customers who choose to implement features of Microsoft SQL Server, such as Partitioning, should conduct adequate testing to ensure that the applications and database behave as expected." - ie, Oracles' generic release.

Oracle HAS, on the other hand, fully documented Partitioning for Oracle Databases - so you might get some further, more valid information with Document ID 627895.1 - but remember, this is a little old now, and Oracle hasn't updated this to include the new functionality for Oracle 12c.

In summary - Partitioning makes behind-the-database management operations easier because you're working with files that might be smaller - or will increase performance because seeking records with multiple smaller files performs better than a single large file. Partitioning is implemented at the Database level, and should be transparent to E1.

As far as I understand, the functionality from ArcTools and PurgeIt are methods to ARCHIVE data - ie, take the data out of the transaction tables and place the data into an online "near" table. However, to access that data, the logic needs to be "aware" that the data is in a different location. Usually this is done on older financial records that you no longer need day-to-day access, but you need it "nearby" in case someone requires the data restored.

Kind of like taking Sales Order information out of F4201/F4211 and placing it into Sales Order History (F42199). The information is still available in the system (for example, a Pricing Engine can create discounts based on a customers entire order history) - but processes that use the active order tables (such as Sales Update, Invoicing etc) aren't slowed down by the older sales history. But if you look at what orders are "open" - you only see the much smaller number of orders in the F4201/F4211. ArcTools and PurgeIt would take really OLD sales order history, for example, and place it into yet ANOTHER table - so Pricing wouldn't see that order history anymore.

Its a functional change that needs a full evaluation and implementation - but it can really help customers with large historical records. But no, thats not "partitioning".

Database Partitioning would take the F42199 for example, and "split" the file into multiple physical files based on, say, Fiscal year or MCU. Querying the entire table will still take the same amount of time - as would writing to the table - but querying based on MCU or Fiscal Year would be tremendously faster.

Hope that helps.
 

cncjay

Well Known Member
Hi Jon,
Thank you for a detailed reply, it gives me a great start. Some years back, at my present company, prior to my arrival, we did an arc tools purge, however, not much data was actually removed from the tables for whatever reasons. We are revisiting them again soon, but would like to do partitioning as well to get the most impact.

Regards
Cncjay
 
Top