DB2 10.1 LUW FP2, new SQL compatibility features

Early this month, DB2 10.1 Fix Pack 2 was released. This fix pack includes a big bunch of new SQL compatibility features, enhancements on data movements, monitoring as well as several enhancements on maintaining your database.

Here's the key compatibility features:

• Distinct type now supports sub-typesm: although there was a workaround for this feature, I'm glad that it's added. It was used by customers extensively.
• PIPE-lined table functions.
• Line number for runtime errors: The long waited for feature.
• You can now monitor routines and debug them much faster with data studio. The debugger has order of magnitude speed-up.
• LTRIM, RTRIM and MOD scalar functions" LTRIM & RTRIM has a second parameter that can be used to specify which character to trim. MOD now goes beyond integers and supports all numeric data types.
• ARRAY_AGG of rows: this is the faster answer for customer running into performance trouble with BULK COLLECT.

I'm glad that DB2 has cut a log way for perfect SQL compatibility.

Passing Netezza exam 000-553

Last week I successfully passed netezza exam 000-553, Netezza Software platform v6.0. It was not an easy exam! If you think about taking this exam, you should be aware of the key points below:

• Short Query Bias.
• Physical components and structure of the appliance. You may be asked in every thing; power supply, number of disks, S-blades, SPA, SPUs, disk controllers, host, FPGA .... etc
• Power supply failure effect.
• Query plans and different ways to show the plans.
• nzload and external tables.
• backups and restore to different versions. (e.g. can u restore to an old version).
• autoReplay option.
• nzstate and nzstats.
• location for different system files. Including log and temp.
• Cursors.
• crontab
• zoneMaps
• CTAS
• Data distribution on different data slices.
• Sequence data type.
• Default user group.
• Isolation level.
• Workload management. 
• Cross database access.
• Materialized views.
• Connectivity; focusing on ODBC.
• HA host states (Active & stand by)

That's what I remember now. Please feel free to post your comments if you have a question.
Thnx.

DB2 10 Best practices documents

Best practices: Storage optimization with deep compression (Authors: Thomas Fanghaenel and Bill Minor)
http://www.ibm.com/developerworks/data/bestpractices/deepcompression/index.html

Best practices: A practical guide to implementing row and column access control (Author: Walid Rjaibi)
http://www.ibm.com/developerworks/data/bestpractices/rcac/index.html

Best Practices: Temporal data management with DB2 (Author: Matthias Nicola)
http://www.ibm.com/developerworks/data/bestpractices/temporal/index.html

DB2 V10.1 Multi-temperature data management recommendations (Authors: Jim Seeger, Karen McCulloch, Naresh Chainani, Kiran Chinta, Aruna De Silva, Vincent Kulandai Samy, and Tom Hart)
http://www.ibm.com/developerworks/data/library/long/dm-1205multitemp/index.html

DB2 V10.1 Query performance enhancements (Author: David Sky)
http://www.ibm.com/developerworks/data/library/long/dm-1205db210performance/index.html

DB2 LUW 10 Release (GA) date

Business insider says that the next major release of IBM DB2 10 LUW (DB2 Galileo) will be out (GAed) on April 30.

Source

Optimised storage utilization and increased further storage saving in DB2 Galileo

Two of the key features of DB2 Galileo (DB2 10 LUW) are Multi-temperature storage and Adaptive compression. Both features come with DB2 Enterprise and Advanced enterprise editions.

Multi-temperature storage can group your storage in hot, warm and cool storage groups. This feature is perfect if you have varying storage requirements. Hot data can be placed on the fastest and most expensive storage where cool data can be placed on a less expensive storage to save more cost.
Also, these storage groups features is integrated with WLM to control priorities of not only special users requests or types of queries but also the type of data. Hot data gets higher priority over warm and cool data.


DB2 has industry-leading compression technologies. As of Galileo, a new Adaptive compression technology was added to further save more storage. Adaptive compression delivers efficient compression ratio of higher volumes of data (new and changing data). The less the size of your data tuples, the more data can brought in memory and the less storage you need. The more data you have in memory the better response and performance you can get from your application.

These new features are wow, I like them ...

I'll talk about the resent security enhancements (LBAC/RCAC),
stay tuned ..

Complete Oracle Application migration to DB2 in a few minutes

Here's one of the greatest migration videos ever. Complete application source code migration from Oracle to DB2.

IBM DB2 10 LUW Announcement

Today IBM announced the latest version of DB2 LUW, the galileo release.

It has many cool features that makes application development and deployment much easier. I have outlined some of these new features in my previous post DB2 LUW 10 (Galileo) new features.

Also, there are some good news for DB2 express-C users. Memory limit has been increased to 4 GB. Processor core limit has been increased to two. Both limits are per physical server, or where partitioned, virtual server. Also, it includes the time travel table feature.


Here's the announcement letter

DB2 SQL PL, looping over cursors

The simplest way to loop over a cursor in SQL PL is for loops. Consider the following example:

for i as cursor1 cursor for SELECT ID,SQLTEXT FROM  test;
    do
      insert into session.Rslt values (ID, SQLTEXT , '0');

      commit; 

 end for;

You can achieve the same result by:
declaring an exit handler for not found:
   DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET exitcode = 1;

and in the procedure code, check for the existcode to exit, something like this:
REPEAT
     fetch cursor1 into v_Tid, v_Tsql;
     insert into session.Rslt values(v_Tsql, v_Tid, '0');
     commit;
     UNTIL exitcode = 1
 END REPEAT;

DB2 LUW 10 new features

DB2 LUW 10 (Galileo release) offers many new features and improvements. Some of the key features are:

• Better Performance
• Time travel query
• Mutli-temperature data
• pureScale now included as a feature
• Adaptive compression
• Continuous Data Ingest (CDI)
• NoSQL graph store

More details about these features in upcoming blogs, stay tunned ...

DB2 DBMS_OUTPUT.PUT_LINE issue, output to console is truncated

When the command SET SERVEROUTPUT ON executes, it calls the DBMS_OUTPUT.ENABLE procedure with the default buffer size of 20000 bytes and sets an internal flag in the client application. When this flag is enabled, the client application calls the GET_LINES procedure after executing each SELECT or CALL statement, and redirects the messages from the message buffer to standard output.

So, when you try to DBMS_OUTPUT.PUT_LINE many times in your script, the output gets truncated if it exceeds that buffer size.

To increase the DBMS_OUTPUT buffer size, call DBMS_OUTPUT.ENABLE procedure with a larger buffer size after executing SET SERVER OUTPUT ON, for example: CALL DBMS_OUTPUT.ENABLE( 500000 );

IBM DB2 10.1 release

DB2 Galileo is the next release of IBM DB2. It has lots of new features, the announcement may be very soon.

Stay tuned ...