batch Archives - DAXRunBase

Data histogram equalization

The Document Attachments were stored in our AX 2012 database, and were occupying several hundred GBs. As part of the migration to D365FO we had to export these files in bulk. The most efficient way is to run the batch task in parallel. But this data is tilted in a way that if you would group the tasks based on their creation date, it the first tasks would barely have any records to process while the last ones would go on forever. The solution for this is called data histogram equalization.

It would be a big challenge to code this in X++, but SQL Server has a function for doing this exactly: NTILE.

The following direct query is able to process the data histogram, and then return 10 buckets of Record Identifier ranges of roughly equal size:

WITH documents (Bucket, RecId)
AS (
       SELECT NTILE(10) OVER( ORDER BY DocuRef.RecId) AS Bucket
              ,DocuRef.RECID
    FROM docuRef
        INNER JOIN docuType
            ON  (docuType.dataAreaId = docuRef.RefCompanyId OR DOCUREF.REFCOMPANYID = '')
                AND docuType.TypeId = docuRef.TypeId
        INNER JOIN docuValue
            ON  docuValue.RecId          = docuRef.ValueRecId                
       WHERE  docuType.TypeGroup         = 1 -- DocuTypeGroup::File
        AND docuType.FilePlace           = 0 -- DocuFilePlace::Archive
        AND docuType.ArchivePath  <> ''
              AND docuValue.Path                = ''
       )
 
SELECT Bucket
       ,count(*) AS Count
       ,(SELECT MIN(RecId) FROM documents D WHERE D.Bucket = documents.Bucket) AS RecId_From
       ,(SELECT MAX(RecId) FROM documents D WHERE D.Bucket = documents.Bucket) AS RecId_To
       FROM documents
       GROUP BY Bucket

Here are the results for spawning 10 batch job tasks to do parallel execution based on the RecId surrogate key index, with ~57230 rows in each bucket. This allows you to evenly distribute the load for data processing in parallel.

If we would export our document attachments sequentially, it would take roughly 40 hours total. By utilizing the data histogram equalization we could get it down to be under 3 hours. That is amazing!

It is a great way to split ranges of data for parallel processing, to keep in mind for the future.

You can learn more about the functionality here:

https://www.sqlitetutorial.net/sqlite-window-functions/sqlite-ntile/

By DAXRunBase|2022-03-31T16:42:48+02:00March 31st, 2022|Categories: AX 2012, Dynamics 365 for Finance and Operations, MSDyn365FO|Tags: #MSDyn365FO, AOT, AX 2012, batch, metadata, SQL, X++|0 Comments

Force executing batch jobs

There is an old, but good article posted by Tariq Bell about the inner workings of the AX Batch framework. When you are working with Dev and Test instances and enable/disable the Batch flag in Server configuration, it might take a while before your tasks are starting to run. There is a simple trick to force executing batch jobs.

As explained in the reference article, each batch task and the framework itself triggers this call, when they are ready to finish execution and a new task needs to be picked up. You could just write a simple job and trigger it with a Menu Item on Server side to kick off a batch job earlier.

static void Job1(Args _args)
{
    BatchRun::serverGetNextTask('01@YOURSERVERNAME');
}

Providing the correct instance number and the server name is required in order to force executing batch jobs.

We have used this method in part to change the batch framework itself, and allow execution of jobs faster than the typical 1 minute schedule. I wanted to post about those findings, but unfortunately that code is not compatible with more recent versions of AX 2012 R3 CU10+, due to Microsoft introducing the new SP_GETAPPLOCK calls in the framework.

By DAXRunBase|2022-03-31T13:16:31+02:00March 31st, 2022|Categories: AX 2012, Microsoft Dynamics AX|Tags: AX 2012, batch, framework, X++|0 Comments

Enable batch e-mail alerts from code

The setup of notifications for batch jobs are quite simple if you know where to look. To enable batch e-mail alerts from code all you need to do is create new records under \\Tables\BatchJobAlerts with the right flags for an AX user, who has their e-mail address set in the User Options correctly.

The following X++ Job is self-explanatory on how to enable batch e-mail alerts from code in case of Cancellation or an Error:

static void WIK_enableBatchJobAlerts(Args _args)
{
    #Batch
    Map             alertsMap;
    Map             alertsEntity;
    BatchJob        batchJob;
    BatchJobAlerts  batchJobAlerts;
    
    while select batchJob
        where (batchJob.Status      == BatchStatus::Executing
            || batchJob.Status      == BatchStatus::Waiting)
            // Optional filtering for dedicated batch service account only
            && batchJob.createdBy   == 'axbatchuserid'
        notexists join batchJobAlerts
            where  batchJobAlerts.BatchJobId    == batchJob.RecId
                // Include all batch jobs where e-mail alert is disabled for our account
                && batchJobAlerts.UserId        == batchJob.createdBy
                && batchJobAlerts.Email         == NoYes::Yes
    {
        // This can be called any number of times to alert multiple users even with different settings
        alertsMap = BatchJobAlerts::addAlertsToMap(
            batchJob.createdBy, // Whom to alert
            NoYes::No,          // Ended
            NoYes::Yes,         // Error
            NoYes::Yes,         // Canceled
            NoYes::No,          // Popup
            NoYes::Yes          // E-mail
            );
        
        BatchJobAlerts::saveAlerts(batchJob.RecId, alertsMap);
        
        alertsEntity = alertsMap.lookup(batchJob.createdBy);
        
        info(strFmt('Enabling alert (Ended=%1 Error=%2 Canceled=%3 Popup=%4 E-mail=%5) for user <%6> and batch <%7>',
            enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#batchJobEnded)),
            enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#batchJobError)),
            enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#batchJobCanceled)),
            enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#popup)),
            enum2Symbol(enumNum(NoYes), alertsEntity.lookup(#email)),
            batchJob.createdBy,
            batchJob.Caption
            ));
    }
}

The output will be something like this:

Enabling alert (Ended=No Error=Yes Canceled=Yes Popup=No E-mail=Yes) for user <sa.axbat> and batch <ADMIN – Due date alerts – every 15m>

By DAXRunBase|2016-02-17T16:03:08+01:00February 17th, 2016|Categories: AX 2012|Tags: alerts, AX 2012, batch, job, X++|1 Comment