How Can We Help?
AIT will not let you run the Year-End Close (YEC) as ‘sa’ or ‘DYNSA’ because of some very old programming in the GP YEC stored procedure. If you run the YEC as ‘sa’, GP will drop indexes on the GL20000 and GL30000 tables. This is old school and a hold-over from SQL 6.0, where the YEC couldn’t finish, was very, very slow and could fill up the log file unless you dropped the indexes.
The downside to dropping the indexes, however, is that, if you have a trigger on the GL20000 or GL30000 table, each trigger will execute a table scan (non-indexed and therefore very, very slow) for every GL distribution that moved from open to history. In the case of a table update, since a SQL update actually performs a delete followed by an insert, two table scans could be initiated for each GL distribution. This could make the YEC very, very, very, very slow.
We ran internal tests to see if the dropping and recreating of the indexes indexes actually has an impact on the YEC processing with a database containing 2,000,000 GL transactions in the year to be closed and 1,000,000 in the last historical year. Benchmarks indicated that they both completed the same time both with and without dropping the indexes, so the drop/create in GP is not necessary.
Because AIT has triggers on those tables, we have to prevent those indexes from getting dropped, and the simplest way to do that is to prevent running it as ‘sa’ or ‘DYNSA’, since those users are the only GP users that would have the rights to drop or create an index. To run the YEC with AIT installed, simply log in as a standard GP user.