Data Replication 2
Results 1 to 5 of 5

Thread: DB2 AS/400 DISAM files - maintenance neglected - over 650 GB

  1. #1
    JamesKerr is offline Junior Member
    Join Date
    Feb 2011
    Posts
    3
    Rep Power
    0

    DB2 AS/400 DISAM files - maintenance neglected - over 650 GB

    Good morning,

    I have a bit of a disk space situation that I am not sure how to handle. We have Attunity AIS configured to read from our I5 DB2 database and write to DISAM files on a Windows 2003 Server. Then we use Attunity CDC for SSIS to pull that data into SQL Server. I had the primary support person for this product leave our company, and during his transition we were unable to locate the documentation to maintain these files. At this point, I have DISAM files consuming 650 GB of disk space on my Windows Server, with one specific DAT file consuming 521 GB with its IDX file consuming 94.5 GB. This is on a 2 TB drive, shared with our primary SQL Server databases. I cannot grow this drive any longer due to limitations of the file system chosen when the system was built. I only have about 30 GB left on this drive until I run out of space completely. I have run out of space entirely a few weeks ago and caused a significant, multi-day outage, so I would like to do something before I run out this time.

    Am I able to delete and re-create the DISAM files, re-initializing them to a closer date? I am afraid that if I try the DPACK process that I will have insufficient space and run the disk out again. If I don't have any other choice, I can offline my SQL database and move files to free additional space, but that is a fairly significant to the business.

    Really, just one table in my solution is my problem. This table is consuming 616 GB out of the entire 650 GB used by the rest of Attunity. Please help! Thank you!

  2. #2
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    The first thing you need to check is how much data there is actually on that file - use a SELECT COUNT(*) on the corresponding staging area table and you will find out. If the actual # of rows in that table is small then the DPACK would not require too much diskspace.

    Another option worth trying for the process of reducing the file size is to use the NTFS compression attribute on the IDX and DAT files before packing them. This will compress the files on-disk leaving much more space free for the new version. This will be somewhat slower but, on the other hand, it should be a simple process. Just make sure the new file is not compressed as this won't give good performance.

    Let us know how it worked for you...
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    JamesKerr is offline Junior Member
    Join Date
    Feb 2011
    Posts
    3
    Rep Power
    0
    Thank you for the response!

    I ran the count query, and it returned 489,125,622 rows, with 162 columns per row. The .dat and .idx files grew 5 GB in the last 18 hours. I don't believe that I will have the capacity to run this DPACK unless I start moving SQL Server Database Files.

    Is there a method for recreating all the files for this solution from a specific starting point? Let's say I set the Service Context in the solution to 3 days ago, just for safety, can I do something to reinitialize the files from that point forward?

  4. #4
    Join Date
    Sep 2006
    Posts
    233
    Rep Power
    10
    James,

    The numbers you give ~500 million rows, each of ~150 columns correlate with the ~600GB DISAM file size so DPACK would not gain you any significant savings... what is eating your storage is that you keep changes too long back - changes that you are probably never going to use anymore.

    A rough calculation (assuming average rates) shows that the files grows about 7GB per day so you may now have there almost 3 months worth of data. How many days back do you really need?

    One option you can try is to save the table definition under a different name with a new physical file location and then try to do INSERT INTO ... SELECT ... WHERE CONTEXT>? query from the old table to the new one, using the CONTEXT column to specify the starting point (you probably want to first use it with EXPLAIN to make sure it uses the index as sequential scan would take too long).

    I encourage you to approach your support contact to help you with this process.


    Quote Originally Posted by JamesKerr View Post
    Thank you for the response!

    I ran the count query, and it returned 489,125,622 rows, with 162 columns per row. The .dat and .idx files grew 5 GB in the last 18 hours. I don't believe that I will have the capacity to run this DPACK unless I start moving SQL Server Database Files.

    Is there a method for recreating all the files for this solution from a specific starting point? Let's say I set the Service Context in the solution to 3 days ago, just for safety, can I do something to reinitialize the files from that point forward?
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  5. #5
    JamesKerr is offline Junior Member
    Join Date
    Feb 2011
    Posts
    3
    Rep Power
    0
    Dror,

    Thank you again for the assistance! I believe I might have found some of the reason for the issue. I have data going back to February in the file, but my router is configured for an eventExpirationHours setting of just 96 hours. I cannot locate a setting for maxdeletedEventsInBatch, but I believe the issue is that we simply are not deleting records from this system as quickly as we are inserting them, and over time, we have grown to this incredible size. I am probably going to start running delete statements, deleting 10,000 rows per batch, and see how quickly that runs, and how intense that is on the system. Hopefully I can get back down to a small number of records, and then I can run a DPACK.

    Thank you again for all the help!!

    James Kerr


    Quote Originally Posted by DrorHarari View Post
    James,

    The numbers you give ~500 million rows, each of ~150 columns correlate with the ~600GB DISAM file size so DPACK would not gain you any significant savings... what is eating your storage is that you keep changes too long back - changes that you are probably never going to use anymore.

    A rough calculation (assuming average rates) shows that the files grows about 7GB per day so you may now have there almost 3 months worth of data. How many days back do you really need?

    One option you can try is to save the table definition under a different name with a new physical file location and then try to do INSERT INTO ... SELECT ... WHERE CONTEXT>? query from the old table to the new one, using the CONTEXT column to specify the starting point (you probably want to first use it with EXPLAIN to make sure it uses the index as sequential scan would take too long).

    I encourage you to approach your support contact to help you with this process.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •