Category Archives: Db2 Built-in Stored Procedures

DB2 10.1 LUW Certification 611 notes 1 : Physical Design

June 3, 2013 1:00 pm / Leave a Comment /

As part of my career development it has been on the cards for me to do some certification at my current company for about four and a half years, and now they are giving me some time for it again. As you know from a previous post then I have being trying to revise for this using something that IBM published just after DB2 10.1 LUW came out and it is very verbose and boring (sorry). It seems more like a technical manual of all the ins and outs of the application than revision guide. There are now some better resources produced and can be found here. This blog post is going to cover some of the things I learn’t from Part 2: Physical Design which can be found here, please take the time to read the doc for yourself it is full of good stuff some of which I think I will be trying out in future.

Physical Design – Exam notes

WITH CHECK OPTION

This seems to be a crafty little three word clause that can add a little security to your views, but it seems at the cost of having more of a maintenance overhead for an application. The example in the document says “WHERE dept = 10“, and goes on to say how if you tried the insert for any other dept no it would not work. This is good as it would stop insertion for dept 11, but would mean that you would need one of these views for each “dept” that you needed.

Informational Constraints

I have come across these before still not really sure what they are useful for, and there is not a good explanation of them in this doc. If they are ENFORCED then how is this different from a check constraint? and if it is ENABLED QUERY OPTIMIZATION then why would you want to make it difficult to find the wrong data rows?

Storage Groups / Multi Temperature data

This is a new feature to DB2 LUW 10.1 and I have found it hard so far to get a clear definition of how the original paths you supply a create database for its data paths and automated storage relate too multi temperature data storage groups. This doc does not make it as easy as I think I have now got it but I will give it a go in a series of bullet points (how I understand it):

  • The data paths that you define when you create a database go to create the default storage group : IBMSTOGROUP.
  • You can then create additional storage groups using the CREATE STOGROUP command for hot etc data
  • The three basic tables spaces that are created at database creation (USERSPACE1, TEMPSPACE1 and SYSCATSPACE1) are created in the default IBMSTOGROUP, that is on the paths that you specified to begin with.
  • Tablespaces are then created USING the newly created storage groups and rather than the data being stored in the default data path it is stored where specified.
  • If you are lucky enough to have workload manager then Storage groups can be given tags to optimize the performance of this – bonus.
  • As I understand it though if you only specify one path in you storage group then you data will not be striped as only one container will be created
  • You then create your partitioned tables (or tables) in these tablespaces appropriately. So the hot part of your partitioned table will go in the hot STOGROUP attached tablespace.

So hopefully the above makes this a little clearer, it does for me. We are currently looking at completely re architecturing our data-pile at the moment and a lot of the design decisions that this offers will be taken into consideration.

Range Clustered Tables

This seems like a most useful feature for star schema data warehouses where there are surrogate or business keys that are generally some form of whole number (SMALLINT, INTEGER or BIGINT). There does seem to be two massive GOTCHA’s in the text though (taken from the text):

  1. DB2 pre-allocates the disk space required for the RCT at creation time. This is done by calculating the number of distinct key values and multiplying it by the table row size. This mandates that the space required for holding the entire table should be available at creation time
  2. you cannot issue an ALTER TABLE statement to alter the physical characteristics of an RCT after its creation

Now I can see this being an issue if you have the full range of a BIGINT * the size of the table fields for each row allocated and used at time of creation, but you can limit this by using the “starting from” and “ending” clauses to the key sequences.

Range clustered tables also have another useful clause of “DISALLOW OVERFLOWS”, this apparently means that reorganization operations are not required, but you will not be allowed to insert any rows that exist outside the limits that you have set, so could be another way to stop GIGO.

Admin_move_table

This seems a most useful stored procedure for moving tables from say DEV to PRODUCTION or if you find that your creation script has accidentally left the wrong table space in the statement and your table needs moving out. Tables can be moved online but this takes more resource in terms of processor and disk space. Most interesting in terms of keeping your data online and generating a new compression dictionary (and therefore also a reorg?). The full command can be found here

Temporal Tables

These are new and will defiantly feature when we re-architecture the data dump that we have currently. I have had to code logic to capture the versions of the subject and then other objects to find the latest version efficiently across multi million row data sets. The new temporal tables will take care of this for you, and still allow you to access the old data in a nearly standard query. The other bonus is that these tables can also be partitioned, but you must make sure the history table can cope with the rows that will be inserted into it.

Conclusion

Well these are my high level take away’s on the extra stuff that seems to be on the paper compared too the V9.7 exam (that I never got round to sitting). There seems to be a lot less detail in this paper compared to the one that has taken me so long to read as it contain so much detail, which leaves me to wonder is the exam a middle ground of the two? Would highly suggest reading the serise and I will be doing more posts in the mean time on the other papers in the set, found at the link at the top of the article.



Posted in: ADMIN_MOVE_TABLE, DB2, DB2 Administration, DB2 Built-in Stored Procedures, DB2 DBA Certification, DB2 DBA Exam 611, IBM, IBM DB2 LUW, Physical Design, Physical Design / Tagged: , , , , , , , , , , , ,

Getting an estimate – DB2 LUW V10.1 Compression

May 20, 2013 8:00 am / Leave a Comment /

So you want to add compression to your house you need to get a tradesman in to give you an estimate, then carry out the work, DB2 can do all of this. Just like building an extension you need to make sure that you need all the appropriate permissions from the “council” (IBM) in place, you either need to buy the Storage Optimisation as a “feature” or as part of Advanced Enterprise Edition of DB2. Please be careful when trying to use compression because as soon as you include “COMPRESSION YES” it will set the features used to YES for compression and if you get audited you could face a hefty bill.

Benefit’s to extending to compression

At a high level the there are three ways of looking at this.
No compression
Benefits
Not having to pay the licensing fee to IBM for compression.
Costs
Large amounts of disk space used for the data, minimal amounts of data in your bufferpools as the page sizes are not made any smaller
Classic Compression
Benefits
Data is compressed on disk and saves you here, data is also compressed in the bufferpools so more pages in them; less I/0 quicker queries. Data is also compressed in the backup images.
Costs
Licensing fee to IBM. Slight increase in CPU usage for the compression dictionary usage. You need to reset the dictionary with a REORG from time to time to make sure that you get the most out of the compression.
Adaptive Compression
Benefits
Data is compressed on disk, data is also compressed in the bufferpools so more pages in them; less I/0 quicker queries. Data is also compressed in the backup images. Data is continually compressed, no need for the RESETDICTIONARY REORG in the same way as the Classic compression.
Costs
Licensing fee to IBM. Increase in CPU usage for the compression dictionary usage. Only available in the latest DB2 V10.1

Here’s what you could be saving – SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO

Handley IBM have included a very useful table function SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO. The full information for this can be found in the information centre here. This table function will estimate the savings that you will get with no compression, “standard” compression and adaptive compression, GOTCHA’s for this are below:

SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO – GOTCHA’s

  1. Tables that are partitioned will come through the stored procedure as multiple rows. You do get a partition ID which you will be able to either join out too or look up in the table SYSCAT.DATAPARTITIONS.
  2. If the table has an (or more) XML column then you will get an additional row in the results returned, a “DATA” and an “XML” compression estimation row. Together with the other gotcha you could end up a lot of a rows returned for a partitioned table with XML columns.

Getting an estimate – SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO

This procedure can be used to get information on either a table or an entire schema, obviously the later can take some time to run from what I have found especially when the tables are large. The most simple form of the stored procedure is:


SELECT * 
FROM TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO({SCHEMA NAME}, {TABLE NAME}))

This will get you a result a little like this (sorry for the formatting):


TABSCHEMA   TABNAME   DBPARTITIONNUM   DATAPARTITIONID     OBJECT_TYPE     ROWCOMPMODE     PCTPAGESSAVED_CURRENT     AVGROWSIZE_CURRENT     PCTPAGESSAVED_STATIC     AVGROWSIZE_STATIC     PCTPAGESSAVED_ADAPTIVE     AVGROWSIZE_ADAPTIVE    
------------        ----------     -----------------     ------------------  --------------  --------------  ------------------------  ---------------------  -----------------------  --------------------  -------------------------  ---------------------- 
SCHEMA         TABLE      0                  0                   DATA            S               0                         495                    65                       173                   65                         170

The example above shows that this table currently is using “Classic” compression, represented by the S, a blank would mean no row compression and an A would be the new adaptive compression in DB2. As you can see it gives you an estimate on the average row size in the different compression modes, this is in bytes and you will then need to work out what the full Gb / Mb size might be based on the cardinality of the table.

The table function is telling us though that there are potentially 65% savings to be made in both adaptive and classic compression, but there is a 3 byte difference and adaptive compression in my opinion is far better so I would ALTER TABLE to COMPRESS YES ADAPTIVE.

If you want to run the table function against a schema leave the table part a blank string


SELECT * 
FROM TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO({SCHEMA NAME}, ''))

This will get you a row per table in the schema (plus any extra for XML / partitioned tables)

The future

In a future post I will look at using this table function to record the values for all tables, you can then look at a before and after and therefore prove that the change in compression and the associated REORG’s have worked.



Posted in: DB2, DB2 Administration, DB2 Built in commands, DB2 Built-in Stored Procedures, DB2 Maintenance, db2licm, IBM, IBM DB2 LUW, SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO / Tagged: , , , , , , , , ,

Lazy RUNSTATS using SYSPROC.ADMIN_CMD

November 3, 2012 12:00 pm / Leave a Comment /

So if you follow my Twitter @dangerousDBA will know that I will do anything for an easy life, and where I work thee range of DB2 skills is very varied and so making things as simple as possible is always needed. To this end using the SYSPROC.ADMIN_CMD it is possible to make this as simple as possible without knowing all the ins and outs of of the actual command.

This first one then is just a simple runstats that will runstats on all indexes and columns.


CREATE PROCEDURE DB_MAIN.RUNSTATS  (IN IN_TABLESCHEMA VARCHAR(100), IN IN_TABLENAME VARCHAR(100))
LANGUAGE SQL
BEGIN
DECLARE RUNSTATSTMT VARCHAR(255);

SET RUNSTATSTMT = 'RUNSTATS ON TABLE ' || IN_TABLESCHEMA || '.' || IN_TABLENAME || ' ON ALL COLUMNS AND INDEXES ALL ALLOW WRITE ACCESS';

CALL SYSPROC.ADMIN_CMD(RUNSTATSTMT);

END

As you can probably guess DB_MAIN is the schema that I keep all the stored procedures and tables in for maintaining the DB2 databases in. So this is easy for anyone who wants now do a total runstats on any table in the database. The second one that I created is a little more fine grained. This one runstats on all columns, but also only on an index specified so will run a little quicker.

CREATE PROCEDURE DB_MAIN.RUNSTATS_INDEX   (IN IN_TABLESCHEMA VARCHAR(100), IN IN_TABLENAME VARCHAR(100), IN IN_INDEX_NAME VARCHAR(255))
LANGUAGE SQL
BEGIN
DECLARE RUNSTATSTMT VARCHAR(1000);
SET RUNSTATSTMT = 'RUNSTATS ON TABLE ' || IN_TABLESCHEMA || '.' || IN_TABLENAME || ' ON ALL COLUMNS AND INDEXES ' || IN_TABLESCHEMA || '.' || IN_INDEX_NAME || ' ALLOW WRITE ACCESS';
CALL SYSPROC.ADMIN_CMD(RUNSTATSTMT);
END

There is not a great need to run the statistics on the columns when you are after just the index, but when in Rome. Obviously you can change these to suit your needs and take out the column stats on the index SP.



Posted in: DB2 Administration, DB2 Built in commands, DB2 built in functions, DB2 Built-in Stored Procedures, IBM DB2 LUW, Runstats, Stored Procedures, SYSPROC.ADMIN_CMD, V10 / Tagged: , , , , , , , , ,

DB2 10 for Linux, UNIX and Windows Bootcamp – Day 4

September 24, 2012 9:56 pm / Leave a Comment /

Last day on the course, realisation of leaving the most excellent Hursley campus that reminded me a lot where I went to University at Keele, and that the migration from DB2 V9.7 to V10 on Tuesday. As promised on Thursday here is the late post for the final day of the course. A lot was covered on the last day so a quick round up

DB2 10 for Linux, UNIX and Windows Bootcamp – Day 4 – What have we done – my prospective

Enhancing Analytic’s with DB2

So the new tools that fall under this section were my main interest in this section, but that’s not to belittle the other work that IBM have done in this area with the improved index scanning and query performance.

You can now create JAVA based table functions that could be called to return the content of a flat file into the FROM part of your query. So this means that you could at the most basic level have something like :


SELECT *
FROM TABFUNC.GET_DATA_CSV('Some File Name') AS (Col1 Integer, Col2, Varchar(20))

It would then return you a table that can be queried and used in a sub-query etc

Continuous Data Ingest for Large Environments

The new INGEST utility, now there was a little bit of confusion over the usage according to the instructor, needing AESE edition of DB2, but the material supplied on the course says it is no additional cost! The hands on lab showed it off brilliantly, with the light ETL ability of the tool, I cant wait to start to use it.

DB2 pureScale

So this is touted as being better than HADR as it is nearly impossible to take offline unless major site failure and now to get round that IBM have developed the product offering to be geographically dispersed with examples that they can give on request of getting it too work over 30 to 40 km. This means that your OLTP processing system can have more 9’s added to the end of your 99%.

Workload Management in DB2

IBM have introduced a new way too regulate your users, along with all the old features there is now a way to use your storage groups to help this out. With a storage group is the new concept of a DATA TAG. This DATA TAG you can give your storage groups a relative higher or lower priority. Therefore the SSD storage groups (this quarters data) could have a DATA TAG of 1 and a SATA drive (this years data) could have a DATA TAG of 5 and a “really” old PATA drive (past five years data) to a TAG of 10. This then means you could have two queries, one that touches the most recent data with the DATA TAG of 1 being set up in Workload manager to run a lot quicker, than the other query which compared this month to a month five years ago, would get limited and “retarded” as it has hit the DATA TAG of 10, which is relatively lower than 1.

Data Concurrency & DB2 pureXML Overview

There has not been a lot change here so I have grouped these two topics together as I cant think of anything to mention here, sorry IBM.

Bootcamp Overview and Exam results

This was very worth going thank you to Mark for presenting and proctoring the exams. It gave a great overview of the new features, and hands on experience of features that I do not get to use at work. I am looking forward to moving tomorrow on to V10 and testing out new features like adaptive compression. I also passed the “DB2 V10 fundamentals” exam, so maybe at some point I will get round to doing the DBA exam.



Posted in: Bootcamp, Continuous Data Ingest for Large Environments, Data Concurrency, DB2 Administration, DB2 Built in commands, DB2 built in functions, DB2 Built-in Stored Procedures, DB2 Data Types, DB2 Development, DB2 pureScale, DB2 pureXML Overview, Enhancing Analytic's with DB2, IBM DB2 LUW, V10, V9.7, Workload Management in DB2 / Tagged: , , , , , ,

DB2 10 for Linux, UNIX and Windows Bootcamp – Day 3

September 20, 2012 11:11 pm / 3 Comments /

It looked like today was not going to be useful, with the agenda for the day looking at the differences between Oracle and DB2 and trying to get you to convert, and security, with a test at the end that was really for business partners so looked like I could take a nap, but actually each part of the day had some useful content.

DB2 10 for Linux, UNIX and Windows Bootcamp – Day 3 – What have we done – my prospective

Breaking free from Oracle with DB2 10

So this from the title sounded like it would have absolutely no content that was of relevance to me, but it had several things in it that will be very useful, if not straight away. The first is that there are features developed for DB2 compatibility with Oracle, but you can use them without having the compatibility mode (ORA) on! One of these was DB configuration parameter of AUTO_REVAL can be set DEFERRED_FORCE and this will mean you don’t have to create your database objects in order e.g. you can create a view that uses a table and then create the table.

The second was that you can turn parts of compatibility mode on without having to have the full ORA or MYSQL set, this means that you could just take advantage of the TRUNCATION command that IBM developed for Oracle compatibility can be used, most useful no more LOAD into table from /dev/null!

IBM have created a free tool where you just have to supply the appropriate drivers and it will move data from nearly any mainstream database (MS SQL, MySQL, Postgres, Oracle, etc, etc). The IBM Data Movement tool looks very useful, as it has many useful features and functions from getting data from your other databases to your DB2. It will generate DDL’s and export and load data, loading takes place through files or pipes and will generate files so you can review progress and see what it has done.

DB2 Security

So this was a little boring but I got two things I got out of this ROLES, which are not new and TRUSTED CONTEXT’s. So ROLE’s look useful and if we get chance to design them and implement them properly then it could sort out a lot of issues we have. Then TRUSTED CONTEXT’s look useful for catching people out in either 1) record that the users connection is not coming from the right place 2) stopping people completely using users from the wrong location (or other connection variable). Watch out dev’s at HX.

High Availability and Disaster Recovery

This again is nothing new to us, the extra features that come with DB2 V10 like being able to have multiple standbys and the new “Super Asynchronous” mode. I also questioned the instructor over the delicacy of the standby to not having the copy files present, and the the way it takes tablespaces of line and they get fried and you have to start again, he had a smile and then said that was one of the features and in future it may be addressed; but it was good to know that I am not doing anything wrong and it is non recoverable from that state.

N18 IBM Information Management DB2 10 Technical Mastery Test v3

So this was an exam more for business partners than DBA’s and was free to take, so what the hell, I passed brilliant I have “1 point” on business partner status?

Late post

Tomorrow I will be travelling home so the likelihood is that I will not be able to post tomorrow about day four till maybe Sunday. Have a nice weekend all.



Posted in: Bootcamp, Breaking free from Oracle with DB2 10, DB2 Administration, DB2 Built in commands, DB2 built in functions, DB2 Built-in Stored Procedures, DB2 Data Types, DB2 Development, DB2 Security, High Availability and Disaster Recovery, IBM DB2 LUW, N18 IBM Information Management DB2 10 Technical Mastery Test v3, V10, V9.7 / Tagged: , , , , , ,

Post Navigation

← Older Posts