DB2 - L

 View Only
  • 1.  Trivia question - (for fun) Large table?

    Posted 9 days ago

    Just for fun: 

    • What is a "large" table to you? (in row count or data size - gigs)

    If you don't mind sharing some more information.
    • How are you using this table? (generic info is fine - "store order history" etc.) 
    • Lessons learned? (what's the good, bad, and just hassle of maintaining)
    • For tables like this, where do you spend most of the time? Same question for your maintenance processes.

    Thank you!

    ------------------------------
    Scott Walker
    Navy Federal Credit Union
    ------------------------------


  • 2.  RE: Trivia question - (for fun) Large table?

    Posted 7 days ago

    Ahh ... a fun Friday Qn !

    Personally I look at GB not Card

     

    I also have 2 personal definitions of Large

    1. Large : I want to look at Partitioning this beast so I can manage it's H/K options better (and by parting I mean PBR, not the pretend PBG)
      • Some where in the 20-30GB range for me
      • Might be a bit low for some ppl ... but that's me
    2. Really Large : Shit we don't have enough Temp DASD for the Sort Work files to build all the indices!
      • Depends on the number of indexes
      • Typically though for me it's where Tables & their Indices climb above 300-400GB

    o we have "Compressed" Tables where the Indices are larger than the actual table ��  

      • We run SORTPSI-NO so that we don't get space issues on index builds of parted objects

    o I'm assuming it still works the same but the book of words said that when the Key Rid pairs from the unreOrg'd Parts are folded in it uses an "insert" style algorithm; hence and ironically you can end up with undesirable index page splits

    o So if we Part ReOrg a TabSpace then we follow up and ReOrg all the NPSIs for the table (Given Index ReOrgs are just really I/O it's not too bad)

      • We've a couple of tables that work quite well with DPSIs (ie: SQL Predicate always have the part key) so these tables don't need too much attention which is great because at a ½ TB I don't want to reorg their NPSIs afterwards

     

     

    ZjQcmQRYFpfptBannerEnd
    Regards,

    Nick CIANCI
    Senior Database Administrator (DB2 for z/OS)

     






  • 3.  RE: Trivia question - (for fun) Large table?

    Posted 7 days ago

    PS: how do we use it

     

    The Main Transactional data (Specifics are secret squirrel) Most of our Partitioning is Timestamp Key based

    It actually works quite well for us, inserts by the nature of the key sequence are sequential and clustered.  We have an active Partition we can concentrate ReOrgs on; and the BPs (32K Paged Fixed 1M Frames for the indices) are targeted to hold pages from those active Parts.

     

    The Main challenge is splits on the Index pages ... We believe Db2 should be able to Sequential detect and not do the 50/50 page split ... but it does still seem to 50/50 split so we target those Indices.   Typically some of the inserts are like : 101a,101b,101n,101z,101e,101f,102a,102b,102n,102z,102e,102f, ...   where the alphabetic in the key is a 2nd column.  So the inserts are slightly out of seq (and no we can't change the App) but it's predominantly sequential so you'd think a lot better than a 50/50 split but that's what we seem to be getting.

     

    ZjQcmQRYFpfptBannerEnd
    Regards,

    Nick CIANCI
    Senior Database Administrator (DB2 for z/OS)

     






  • 4.  RE: Trivia question - (for fun) Large table?

    Posted 7 days ago
    Thank you Nick! 

    I am dealing with two giant tables. (Giant to me.)

    The largest of the two tablespaces I am working on in this project is 1.2 TB at 50% compression. 11.+ billion rows.
    They aren't full (5 year retention period and the app is not 5 years old yet. Data is higher in the more recent partitions as the app started off slow. It has leveled off though somewhat but growing. It's trending well above 400 million rows per month). I think we're about 80% there to the 5 year retention? So, probably over 20 billion rows once it's full. 
    They are both partitioned by timestamp and monthly. Thankfully, I have a PBG for active data (30 days) and the rest go into these two archive tables. 

    I have had trouble with doing alter rotation on tables partitioned with dates (or timestamps). These included. I had a fancy rotate partition job set up but that didn't work out so well. So, I'm coming up with a way to consolidate partitions to hold same data in fewer partitions. Then likely need to add partitions so I can do rotate in a planned outage window hopefully only once yearly. 

    I do have plans to replace these with different partitioning scheme but I am waiting to see what the future holds with the application and how the table grows once it's mature (4 years into 5 year retention period). 

    You described some issues that I don't seem to have which is likely due to these being only historical tables and usage is low. 
    I was able to do full tablespace level reorg before to increase DSSIZE. I want to convert them to PBR-RPN soon. 

    The point of my question is dealing with this table and others has taught me a lot. I plan to do a presentation over this topic if I can get enough thoughts together to be helpful to others.

    ------------------------------
    Scott Walker
    Navy Federal Credit Union
    ------------------------------



  • 5.  RE: Trivia question - (for fun) Large table?

    Posted 7 days ago
    We'll, it's Friday so......

    Many moons ago I attended an IBM DB2 training class (remember them?)

    The instructor asked who had the biggest table, and after some discussion it turned out to be "my" 23,000,000 row address table

    Everyone else looked at me like I had two heads

    Agreed, it was a lot for DB2 v1.2, but today???  Pfahhhh. Seems like nothing eh?

    Phil G

    Sent from my iPad





  • 6.  RE: Trivia question - (for fun) Large table?

    Posted 3 days ago
    Thank you Phil. What are your thoughts for a large table today?

    ------------------------------
    Scott Walker
    Navy Federal Credit Union
    ------------------------------