DB Unloading took more time in progressing

[Pothiraj Karuppiah]

Hi Team,

We were in the process of doing maintenance in Automic database tables. And this is AE V10.0.2.

The records are huge in RT and MELD it is around 500 and 40 million records occupied respectively. We ignored DB Archive as it is not needed. Reorganization completed and it took around 2 days to complete.
Now started with DB-Unloading. Its keeps on running for more than 18hrs and we could not see any movement in terms of %. While checking the log it shows some movement but all are insert command like below instead of deletion. 

U0003525 UCUDB: ===> 'insert into DIVDB (DIVDB_PK) select Meld_Idnr from Meld where Meld_DeleteFlag = 1 and rownum <= 1000'

Please someone provide some detail about DB-Unload progress. Is it this going normal or any deviation happened as we could see only insert command. I have attached the Reorg and DB Unload log for your reference. Also I shared the screenshot from DB-Unload progress its still in 0%.

Appreciate if someone explain in detail about DB-Unload progress. 


Regards,
Pothiraj

Attachment(s)

Logs.zip   310 KB 1 version

[Krum Ganev]

Hey there,

In my opinion you had to start with shorted time periods and run the DB maintenance several times rather than 1 BIG RUN.

A good thing to remember when deciding to skip the Archiving is that on both REORG and UNLOAD utility you have to amend the config file.

no_archive_check=

Archiving check. "0" = Checks if data has been archived before. "1" = There is no such check.

From your logs i can see that only in the REORG log, but not in the Unload log. The archive check is slowing down the whole execution tremendeously.

For your sanity check you can execute the below queuery to see if it was deleting any data.

select count(*) from AH where AH_DeleteFlag =1 select count(*) from AH where AH_ArchiveFlag=1 select count(*) from AH where AH_DeleteFlag =1 and AH_ArchiveFlag=1

My advise would be to stop the Unload, alternate the .ini and run it again. As again will take some time to finish you may like to run it in Batch mode rathar than via the GUI.

Best Regards,

Krum

[Pothiraj Karuppiah]

Hi Krum,

Thank you so much for your response.

I executed the given queries and below are the response. There is no single records deleting from AH. Instead I could see from MELD table it is decreasing dramatically. For 10sec it decreases around 2k records.

select count(*) from AH where AH_DeleteFlag =1;
55767331
select count(*) from AH where AH_ArchiveFlag=1;
0
select count(*) from AH where AH_DeleteFlag =1 and AH_ArchiveFlag=1;
0


select count(*) from MELD where MELD_DeleteFlag =1;
4164828
select count(*) from AH where AH_ArchiveFlag=1;
0
select count(*) from AH where AH_DeleteFlag =1 and AH_ArchiveFlag=1;
0

One more information in AE V10.0.2 we can see no_archive_check parameter only at REORG ini and not at UNLOAD ini. So I can add it externally inside UCYBDBUN.ini? and run as batch mode.


Regards,
Pothiraj

[Krum Ganev]

Hey there,

True, in the .ini file this parameter is missing but if you check the documentation of the DB Unload utility you will see it. LINK

It was pointed to me by the Vendor Support Engineer as we also had some issues with the Unload back in the days.

Moreover the other parameters as "suppress_output" , "reorg_mode" are obsolete since v10. You can keep it in the ini or remove it. It doesnt matter anymore.

------------------------------
SME
DXC.Technology
Bulgaria
------------------------------

[Pothiraj Karuppiah]

Hi Krum,

Thanks for your comments and yes it is true I too read the log. So as you mentioned I configured no_archive_check parameter at UNLOAD ini. Also instead of running as GUI I ran it as Automic job. All are moving fine. Once done with the activity will have to perform Rebuilding indexes and shrinking the DB / tables to reclaim the space.

I have one more question does this Automic maintenance will show any performance improvement in terms of regular batch run timing/speed up the process ? 


Regards,
Pothiraj