As always verify the script  before you implement it in your system. You can download the script here : hadr_checker.sh

Please feel free to comment on the script and ways to improve it.

The post Checking on HADR appeared first on techs@work.

Brief Intro :

INGEST from DB2 10 is fast and it enables us to continuously  load  data while giving high availability and recoverability. INGEST is a cross between LOAD and IMPORT. INGEST is optimized to be multithread to process data in parallel.  Unlike LOAD and IMPORT,  INGEST can run continuously reading a data stream through a pipe. This is great, if you are loading live feeds of data from a social media stream or from your  production stream.  Another feature of INGEST is, it can read multiple files as its source to load the data. Apart from the traditional INSERT statement, it can do UPDATE, MERGE and DELETE. This comes in handy if the data you are loading  contain  updates to existing rows .

How does it work ?

INGEST has three threads/parts :

1. Transporter :  This thread reads from the input files or data stream pipe s in parallel and sets the thread to formatter. DB2 spawns one thread for each input files.

2. Formatter: Formatter threads formats and prepares the data to be send to Flusher. Formatter threads make sure the data is set in such a way that INGEST can insert into DB2 in parallel. The number of formatter threads is specified by the num_formatters configuration parameter.

3. Flusher:  Flusher threads insert the data into the DB2 table. If you are using INGEST to load into a partitioned database, then a flusher thread is associated with each partition.  There could be multiple flushers running to increase the throughput of the data ingest. The number of flushers for each partition is specified by the num_flushers_per_partition configuration parameter.

Like I said its completely designed to be run efficiently and give high availability.

Can it recover from a failure ?

Absolutely ! INGEST is capable of restarting from where it failed. It literally uses INSERT statements to load the data. With INGEST, you can specify dumpfile to place any rejected rows due to formatting error or invalid character, exception table to place any rows with constraint violations, warning counts to stop if the threshold is met.  You can also specify commit count in rows or by time. This comes in handy when loading data continuously from a stream. If the INGEST utility gets aborted for any reasons (power failure or somebody accidentally killing the thread, you can restart the INGEST with RESTART option and DB2 will resume the INGEST from the last commit point. If do not want to restart INGEST you can issue the RESTART TERMINATE and DB2 will clean up the failed INGEST. Unlike with the TERMINATE parameter of the LOAD command, data already committed by the failed INGEST command is not removed from the table.

What all format can it handle ?

INGEST utlitiy  supports the following input data formats:

Delimited text
Positional text and binary
Columns in various orders and formats

How about performance ?

Obviously this is one of the most sacred part for the DBA when choosing the option to load lot of data.  The best performance is always LOAD, but it doesn’t give high availability. INGEST even though under the covers issues INSERTS, I have noticed that when loading hundred thousands of rows INGEST was able to perform 40% faster than traditional INSERT. The reason why I compared to INSERT, is because its the only option that gives me high availability.

How is it run ?

Assume we have a coma delimited customer file as follows that needs to be loaded :

10001,”Edward”,”Williams”,”12349 West Ave St”,”Taos”,”NM”,”564-573-4839″
10002,”James”,”Miller”,”43348 Knight St”,”Dumont”,”NJ”,”201-873-4239″
10003,”Jessica”,”Robertson”,”489 River N Rd”,”Austin”,”TX”,”512-534-9859″
:      :     :    :    :   :   :    :    :   :    :   :      :     :    :    :   :   :    :    :   :    :  :      :     :    :
90000,”Kathy”,”Rodriguez”,”8293 Brooksheild Apt, # 201″,”Anahiem”,”CA”,”714-354-775

The customer table is :
CNumber   INT          —> primary key
FName       varchar(50)
LName       varchar(50)
Street         varchar(100)
City             varchar(50)
State           char(2)
Telephone  char(12)

Lets see our options to INGEST the data:

This is the simplest form :
ingest from file customer.del format delimited insert into raju.customer;

If you want to have more control or if the data is in different order than the table column, then you can do this . Note here I have flipped the firstname and lastname on the VALUES:

ingest from file customer.del format delimited
($cid integer external
,$firstname char(50)
,$lastname char(50)
,$streetname char(100)
,$cityname char(50)
,$statename char(2)
,$telephone char(12)
)
dumpfile customer_dump.del
exception table raju.customerexcept
warningcount 100
messages ingest_customer.msg
insert into raju.customer(cnumber,fname,lname,street,city,state,telephone)
values($cid,$lastname,$firstname,$streetname,$cityname,$statename,$telephone);

The exception table should be in place before you run this. You can create the table with :
create table raju.customerexcept like raju.customer  or run the same ddl you used for the customer table.

If your data contains updates to existing rows as well as new rows, you can use MERGE statement on your INGEST :

ingest from file customer_monday.del, customer_tuesday.del format delimited
($cid integer external
,$firstname char(50)
,$lastname char(50)
,$streetname char(100)
,$cityname char(50)
,$statename char(2)
,$telephone char(12)
)
dumpfile customer_dump.del
warningcount 100
messages ingest4.msg
merge into raju.customer on (cnumber = $cid)
when not matched then
insert (cnumber,fname,lname,street,city,state,telephone)
values($cid,$firstname,$lastname,$streetname,$cityname,$statename,$telephone)
when matched then
update set (fname,lname,street,city,state,telephone) =
($firstname,$lastname,$streetname,$cityname,$statename,$telephone);

Note that in the above example, I have two input files.  I have also removed the exception table. DB2 does not allow to use exception table option when using MERGE option. Don’t ask me why they don’t allow ?

If you have a list of rows that you want to delete, then you can do as well :

ingest from file customer1.del format delimited
($cid integer external
,$firstname char(50)
,$lastname char(50)
,$streetname char(100)
,$cityname char(50)
,$statename char(2)
,$telephone char(12)
)
delete from raju.customer
where cnumber = $cid;

How do we restart ?

You can restart a terminated INGEST with the RESTART CONTINUE <job_id> option

If you want the ability to easily restart a terminated INGEST use the option RESTART NEW <job_id> on your original INGEST command like :

ingest from file customer_monday.del, customer_tuesday.del format delimited
($cid integer external
,$firstname char(50)
,$lastname char(50)
,$streetname char(100)
,$cityname char(50)
,$statename char(2)
,$telephone char(12)
)
dumpfile customer_dump.del
warningcount 100
messages ingest4.msg
restart new ‘customeringest1′
merge into raju.customer on (cnumber = $cid)
when not matched then
insert (cnumber,fname,lname,street,city,state,telephone)
values($cid,$firstname,$lastname,$streetname,$cityname,$statename,$telephone)
when matched then
update set (fname,lname,street,city,state,telephone) =
($firstname,$lastname,$streetname,$cityname,$statename,$telephone);

The RESTART NEW option will help you identify the ingest command that got terminated. Once you identify the ingest that got terminated, you can restart it by issuing RESTART CONTINUE

ingest from file customer_monday.del, customer_tuesday.del format delimited
($cid integer external
,$firstname char(50)
,$lastname char(50)
,$streetname char(100)
,$cityname char(50)
,$statename char(2)
,$telephone char(12)
)
dumpfile customer_dump.del
warningcount 100
messages ingest4.msg
restart continue ‘customeringest1′
merge into raju.customer on (cnumber = $cid)
when not matched then
insert (cnumber,fname,lname,street,city,state,telephone)
values($cid,$firstname,$lastname,$streetname,$cityname,$statename,$telephone)
when matched then
update set (fname,lname,street,city,state,telephone) =
($firstname,$lastname,$streetname,$cityname,$statename,$telephone);

The RESTART CONTINUE option will enable us to restart INGEST from the last commit point.

If you want to terminate the utility and want to clean up from the previously failed run, then you can use  RESTART TERMINATE <job_id> option just like the RESTART CONTINUE option.

How can I monitor the utility ?

There are few ways to watch what INGEST utility is doing .  IBM has provided few options:

1. INGEST GET STATS
2. INGEST LIST or
3 you can query TABLE(MON_GET_CONNECTION(NULL,NULL)) and look for the application_name DB2_INGEST

Conclusion

While INGEST has a lot of room for improvement, its a great utility to have when compared to other options. Its easy to use and keeps up the availability while inserting large amount of data. I also like the fact that we can use it to delete rows for a list of values.  I hate to use large IN clause in my delete statements or have to load the key into a temporary table scenario. Few limitation with INGEST you need to aware of , currently INGEST do now allow LOBs, XML, structured types, has lot of restrictions on generated columns. I definitely recommend anyone to try out the INGEST utility and see how you can benefit from it. After all you paid for the product, so the least you can do is try it. Trust me if you use it right, you will love it !

The post A quick overview of DB2′s INGEST appeared first on techs@work.

The post Happy New Year ! appeared first on techs@work.

On V9.5, I have noticed around 70% compression, with V9.7, it slightly increased to 75% compression. But then V9.7 was able to compress the indexes as well.  Galileo (a.k.a V10) adds some more juice to this, it now has adaptive compression.

So what is adaptive compression ?  Prior to V10, IBM used global pattern replacement algorithm. So if the value is found on the global dictionary its compressed. But with adaptive compression, besides compressing based on global pattern, DB2 will now look within a data page to see if there are further compression opportunities by finding locally repeating patterns within a data page. This increased the chance of compressing the data exponentially.  I have seen compression ratio of 85 % on certain tables.

I am not going to explain how the wheels move and turns and all that stuff. Most of us don’t need to know how the algorithm works.  So I am just going to show you how to use it and reap the benefit. After all thats what most of us are after. If you need to know how the algorithm works, I am sure there are many sites that explain it.

First of all  in order to use the compression feature you need to have the license for Storage Optimization or have AESE license. You can do that by doing a db2licm -l and see if the storage optimization is licensed or you have a AESE edition.

Let’s get started and rev up the engine:

1. If you are migrating from previous version of DB2 and /or want to see if you are already compressing tables :

2. To find out how much you will save by compressing in DB2 V10 use the below SQL. Run against the schema you want to evaluate. The currently_pct_pagessaved will show you how many percent of pages are saved currently by compression (if any), the classic_pct_pagessaved shows you how many percent of pages will be saved with classic compression under V10 and the adaptive_pct_pagessaved show you how many percent of pages will be saved with adaptive compression. The storagesize_MB shows you how large the table is :

3. Starting from DB2 V9.7 you can compress index as well. Run the following sql to find out how much compression you will get on indexes :

Now you have the data to take a informed decision. As I mentioned on my previous article on compression , use due diligence before selecting the tables you want to compress.  Generally tables with a read/write ratio of 70 percent or more reads and 30 percent or less writes are good candidates for compression.  If the table gets lot of updates, then you lose cpu cycles on db2 uncompressing and compressing data.   Row compression is a great feature for DSS based systems, where there is lot of data and has more reads than updates.  Row compression performs best in I/O or memory-bound environments where the bottleneck is not on the CPU. Extra CPU cycles are required to perform row compression and expansion of data rows whenever they are accessed or modified. However this overhead can be offset by efficiencies that are gained in doing fewer I/O operations.

Similarly, index compression is another awesome feature , but not all indexes will benefit from compression. Best candidates for index compression are indices with multi column, columns with character data, varchar columns, columns with low cardinality.

4. Once you determine what tables and index to compress , you now need to turn on compression  :

5. Now that you have altered the objects to turn on compression you can either wait for the dictionary to get build automatically by ADC or run one of utilities that will build the dictionary, I recommend you running a reorg if you can :

6. Run the query from step 2 and 3 and see how much space you have saved. Prepared to be amazed.

As always, make sure you get some metrics before and after compressing the tables. Saving storage is great, but whats even more great is saving system resources.  I have noticed I/O wait time go down by 80% in certain cases and logical reads going high because now DB2 can fit more rows in the buffer pools.  CPU cycle does go a bit high,but overall its lower than before because of less I/O operations.

They say a picture says a thousand words.  The below graph shows my % I/O wait before and after I turned on adaptive compression:

You can see how much it has gone down and in some cases it has gone away.  Now do you see why I say its the Bugatti Veyron ! If you haven’t looked into compression, I strongly suggest your try it out.

As always the above is my opinion and I strongly urge you to test it out in your environment to see what benefits you get. I am sure you won’t be disappointed.

The post DB2 V10 Adaptive Compression appeared first on techs@work.

ADMIN_MOVE_TABLE has 5 stages :

1. INIT : During this stage, db2 checks to see if the table move can take place and if the user has the proper authority. If the user passes a target table, db2 creates the table if it doesn’t exist. DB2 creates a staging table to capture any DML that happens during the copy phase. DB2 also creates 4 triggers to capture any data change activity.

2. COPY :  In this phase, DB2 copies the data from source table to the target table. Cursor Stability is used when DB2 reads the data from the source table.  By default, DB2 inserts the data to the target table using regular INSERT, so transaction logs are used to make the process recoverable.  You can optionally use can load the data using a cursor by specifying NONRECOVERABLE LOAD. But then the table might be locked for a period of time and the process us not recoverable. Its advised to take a backup if a load is used. DB2 created all the indexes and other objects

3. REPLAY :  While the COPY phase is in progress, DB2 records changes to source table to a staging table using the triggers it created in the INIT phase.  Once the COPY phase is complete, DB2 replays or merges all the rows in the staging table to the target table. There might be instances that few rows might still be left pending in the staging table.

4. SWAP : On this phase, DB2 swaps the name of the target table with that of the source table.  In other words the source table is given a new name and the target tables is renamed to the actual table name.  DB2 takes an exclusive lock for a brief period on both the source and target table to ensure that no new data change happens while it completes any rows that is still there on the staging table. Once this is complete,  the old source table is then removed or kept according to the option specified.

5. CLEANUP: DB2 now cleans up all the temporary objects it created for this process. It drops the 4 triggers and the staging table and completes the transaction.

At the end of the process, it writes all the details on the systools.ADMIN_MOVE_TABLE.

SYNTAX:  There are essentially two methods to use ADMIN_MOVE_TABLE.  The first method allows you to change certain parts of the table definition for the target table. For instance, if you had a table DDL that is quite large , and all you want to do is modify the table spaces for the table, you can do so without having to determine the entire CREATE TABLE statement needed to re-create the source table. All you need to do is to fill out the data_tbsp, index_tbsp, and lob_tbsp parameters, leaving the other optional parameters blank. You can also use this method to add MDC columns, change column types etc.

The second method provides you with more control and flexibility by allowing you to create the target table beforehand, rather than having the stored procedure create the target table. This enables you to create a target table that would not be possible using the first method.

Method # 1:

>>-ADMIN_MOVE_TABLE--(--tabschema--,--tabname--,---------------->

>--data_tbsp--,--index_tbsp--,--lob_tbsp--,--mdc_cols--,-------->

                                             .-,-------.      
                                             V         |      
>--partkey_cols--,--data_part--,--coldef--,----options-+--,----->

>--operation--)------------------------------------------------><

Method # 2:

>>-ADMIN_MOVE_TABLE--(--tabschema--,--tabname--,---------------->

                      .-,-------.                    
                      V         |                    
>--target_tabname--,----options-+--,--operation--)-------------><

Here is an example that I used to move ORDERS table to a new tablespaces:

CALL SYSPROC.ADMIN_MOVE_TABLE(
‘RAJU’,
‘ORDERS’,
‘ORDERSTBSP’,
‘ORDERSIDXSP,
‘ORDERSLOBSP’,
”,
”,
”,
”,
”,
‘MOVE’)

Here is a short video I attempted to show  how it works. In this video, I will demonstrate  how DB2 ADMIN_MOVE_TABLE moves the ORDERS table from a tablespace IBMDB2SAMPLEREL tablespace to USERSPACE1 while a batch insert is running.

What I am mentioned above is solely my opinion, please do your due diligence before you try this on your system.  Please send me any comments or suggestions on this.

The post ADMIN_MOVE_TABLE appeared first on techs@work.

Here is what you would need to get PHP to work with Postgres:
a. Apache
b. PHP with pgsql option
c. Postgres

If you don’t want to install each of the component by yourself then you can optionally use the stack from bitnami.org Install the appropriate stack for your operating system. If you are using Ubuntu (debian), you can do apt-get install php5-pgsql.

Open up the php.ini file and make sure you have the PostgresSQL extensions turned on.  If you are on Linux the install/compile PHP5 with pgsql . With windows you may have to install the pgsql extensions.

Lets get into postgres and create a database , table and insert some data that we can query using php.

Assuming the userid you are on has  postgres libraries in its environments, open a command prompt.
Create a Database :
postgres@ubuntu:/home/public_html$ createdb rajudb

Get to a psql prompt :
postgres@ubuntu:/home/public_html$ psql

psql (8.4.11)
Type “help” for help.

Connect to database rajudb:
postgres=# \c rajudb

psql (8.4.11)
You are now connected to database “rajudb”.

Create a table called emailaddress:
rajudb=# create table emailaddress(userid char(4) not null,emailid char(25), primary key(userid));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index “emailaddress_pkey” for table “emailaddress”
CREATE TABLE

Insert data into the table emailaddress that you created earlier:
rajudb=# insert into emailaddress values(‘A001′,’a001@techsatwork.com’);
INSERT 0 1
rajudb=# insert into emailaddress values(‘A002′,’a002@techsatwork.com’);
INSERT 0 1
rajudb=# insert into emailaddress values(‘A003′,’a003@techsatwork.com’);
INSERT 0 1

Select data from the table :
rajudb=# select * from emailaddress;
userid |          emailid
——–+—————————
A001   | a001@techsatwork.com
A002   | a002@techsatwork.com
A003   | a003@techsatwork.com
(3 rows)

Now lets try to retrieve the same rows using a web browser through a php script. Make sure your apache webserver is running and php is enabled to connect to postgres. Open a text editor and type the following and save it on the web server directory .

<!DOCTYPE html PUBLIC “-//W3C//DTD HTML 4.01//EN”>
<html>
<head></head>
<body>

<?php
// attempt a connection
$dbh = pg_connect(“host=localhost dbname=rajudb user=postgres password=secret!”);
if (!$dbh) {
die(“Error in connection: ” . pg_last_error());
}

// execute query
$sql = “SELECT * FROM emailaddress”;
$result = pg_query($dbh, $sql);
if (!$result) {
die(“Error in SQL query: ” . pg_last_error());
}

// iterate over result set
// print each row
while ($row = pg_fetch_array($result)) {
echo “User Id: ” . $row[0] . “<br />”;
echo “Email address: ” . $row[1] . “<p />”;
}

// free memory
pg_free_result($result);

// close connection
pg_close($dbh);
?>

</body>
</html>

 This is one set of pg functions that you can use to interact with postgres using php. To find out about others, refer to the php manuals. 

The post PHP & Postgres appeared first on techs@work.

CREATE PROCEDURE spRcustomer_order_history
(IN customerid int, OUT customerorders XML)
DYNAMIC RESULT SETS 1

P1: BEGIN
declare not_found condition for ’02000′;
declare exit handler for not_found
   set customerorders = xmlcast(‘NO ROWS FOUND’ as xml);

#####################################################
— # Returns results in a XML parameter
#####################################################

select xmlelement(name “customerid” , xmlattributes (o.customer_id),
xmlagg(
xmlelement(name “order_id”,o.order_id,
xmlelement(name “order_timestamp”,o.order_timestamp),
( select
xmlagg(
xmlelement(name “product_id”, oi.product_id,
(xmlforest (oi.qty as “qty”,
oi.unit_price as “unit_price”,
oi.product_discount as “product_discount”,
(oi.qty*oi.unit_price-oi.product_discount) as “total_price”
)))) from order_items oi
where o.order_id = oi.order_id
)))) into customerorders
FROM orders o
where o.customer_id = customerid
group by customer_id
;
END P1
@

Now lets call the stored procedure:
Call spRcustomer_order_history(13453,?);

<customerid CUSTOMER_ID=”13453″>
<order_id>1003
<order_timestamp>2012-05-24T21:05:33.552802</order_timestamp>
<product_id>Z001
<qty>10</qty>
<unit_price>10.50</unit_price>
<product_discount>.00</product_discount>
<total_price>105.00</total_price>
</product_id>
<product_id>Z002
<qty>14</qty>
<unit_price>20.50</unit_price>
<product_discount>.00</product_discount>
<total_price>287.00</total_price>
</product_id>
</order_id>
</customerid>

This will retrieve order history for the customer 13453 and return the data in XML format.  Since the data is returned in an XML format, you do not need to use a cursor.  Test it out and me know if there is any errors or a better way to do it.

The post Retrieving data as XML using stored procedure appeared first on techs@work.

1. Reinitialize the subscription (after giving the dba a kick in the you know what)
2. If replication must continue while you identify the source of the error, specify the parameter -SkipErrors 20598 for the Distribution Agent and either fix the subscriber table.
3. Manually insert the deleted rows to subscriber table, so the deletes can flow through
4. If all of the above can’t be done, then delete the offending commands from the MSrepl_commands table. (again give the dba a kick in the you know what !)

The last option should be done with care since you are messing with replication commands that the distributor has pending. The table distribution.dbo.MSrepl_commands table has all the commands the distributor has send and going to send to subscriber. If you delete more than what you need now you would have data inconsistency between publisher and subscriber.  In this post I am going to show you how you can identify the offending command and delete them from the MSrepl_commands table.

First lets find the errors that the distributor has encountered . You can find this either from the Replication monitor or by querying dbo.MSrepl_errors.  From the error_text column you should be able to identify the xact_seqno and command_id . Get the list of the xact_seqno and command_id that is causing the issue.

Second lets see what those commands are . For this execute the sp_browsereplcmds passing the xact_seqno. This will give you the list of all the commands that the distributor is trying to execute. Confirm the once you need to delete.
eg: sp_browsereplcmds @xact_seqno_start = ’0x0000002A000000D3000700000000′

Third  delete the rows from the MSrepl_commands table so that replication can continue with other pending ones. Be very cautious on what rows you delete. You do not want to delete rows that are not causing the issue.
eg:  delete from dbo.MSrepl_commands
where xact_seqno = 0x0000002A000000D3000700000000 and command_id in (1,2)

Once you delete the offending rows from the MSrepl_commands, replication should start sending the commands to subscriber. You the replication monitor to see whether the undistributed commands queue is decreasing.

Use this approach at your own risk.

The post What if you encounter Error number: 20598 with SQL Server replication ? appeared first on techs@work.

create table orders
(order_id  int not null
generated always as identity (start with 1000,increment by 1, nocache)
,customer_id int not null
,order_timestamp  timestamp
,order_channel_id  varchar(5)
,csr_id  varchar(10)
,primary key (order_id)
);
create table order_items
(order_id  int not null
,seq_num  int not null
,product_id  varchar(20) not null
,qty int
,unit_price dec(9,2)
,product_discount dec(9,2)
,primary key(order_id,seq_num)
);

Now that the table is created, lets create the stored procedure that will insert the data to the parent table and all data to the child table. Granted this stored procedure does not have all the bells and whistles and is only intended for demonstrating a method.

create or replace procedure spIorders
(in customerid int
,in orderchannelid varchar(5)
,in csrid varchar(10)
,in orderitems XML
,out orderid int
)
LANGUAGE SQL
NO EXTERNAL ACTION
P1:BEGIN
– declaring global temporary table to hold the data
declare global temporary table session.gtt_orders
(customer_id int not null
,order_channel_id varchar(5)
,csr_id varchar(10)
,order_item XML)
on commit delete rows
not logged;
– insert the data to the global temporary table from the data passed in
insert into session.gtt_orders
(customer_id,order_channel_id,csr_id,order_item)
values
(customerid
,orderchannelid
,csrid
,orderitems);
–insert into parent table (orders) from global temporary table
select order_id into orderid
from new table
(insert into orders
(customer_id,order_timestamp,order_channel_id,csr_id)
select gtt.customer_id,current_timestamp,gtt.order_channel_id,gtt.csr_id
from session.gtt_orders gtt);
–insert into child table (order_items) from global temporary table
–xmltable will split the XML document to multiple rows
–row_number() over() is an olap function that will generate rows number for seq_num
insert into order_items
(order_id,seq_num,product_id,qty,unit_price,product_discount)
select orderid,row_number() over(),x.productid,x.qty,x.unitprice,x.productdiscount
from session.gtt_orders gtt,
xmltable(‘$c/orderitem/item’ passing gtt.order_item as “c”
columns
productid varchar(20) path ‘productid’,
qty int path ‘qty’,
unitprice dec(9,2) path ‘unitprice’,
productdiscount dec(9,2) path ‘productdiscount’) as x;
–
commit;
– dropping the global temporary table since the inserts have be done.
drop table session.gtt_orders;
END P1
@

Now lets call the stored procedure we just created an pass in an XML that has three order items :

call spIorders (5000,’Web’,'RAJU’,
xmlparse(document
‘<orderitem>
<item>
<productid>iPad3-Cell-64</productid>
<qty>10</qty>
<unitprice>829.00</unitprice>
<productdiscount>50.00</productdiscount>
</item>
<item>
<productid>iPad2-Wifi-16</productid>
<qty>5</qty>
<unitprice>629.00</unitprice>
<productdiscount>0.00</productdiscount>
</item>
<item>
<productid>iPod-Tch-8</productid>
<qty>100</qty>
<unitprice>199.00</unitprice>
<productdiscount>100.00</productdiscount>
</item>
</orderitem>
‘ preserve whitespace),?);

If you query the orders and order_items you should be able to see one row in orders table and three rows in order_items.
Please feel free to comment on this and possible improvements or alternate methods.

The post Stored Procedure to insert data to parent and child using XML appeared first on techs@work.

Whats wrong with this ? Nothing wrong with it unless you don’t want to kill the I/O system on a heavily used table.  So I introduced her to the famous  select from insert statement. Yes you heard it right…select from insert.  Surely everybody knows  INSERT SELECT, but  SELECT from INSERT is a slick feature.  You can retrieve the data you inserted without doing any additional I/O.  Here is a small example :

Lets create two sample tables :

create table orders
(order_id  int not null
generated always as identity (start with 1000,increment by 1, nocache)
,customer_id int not null
,order_timestamp  timestamp
,order_channel_id  varchar(5)
,csr_id  varchar(10)
,primary key (order_id)
);
create table order_items
(order_id  int not null
,seq_num  int not null
,product_id  varchar(20) not null
,qty int
,unit_price dec(9,2)
,product_discount dec(9,2)
,primary key(order_id,seq_num)
);

Now lets try to add a row to orders table and then using the generated order_id we will add a row to the order_items table.

create or replace variable vseq int;
set vseq = 1;
create or replace variable vorder int;
set vorder =
(select order_id
from new table (
insert into orders
(customer_id,order_timestamp,order_channel_id,csr_id)
values(5000,current_timestamp,’web’,'raju’)));

insert into order_items
values(vorder,vseq,’A001′,10,100.00,0.00);

set vseq = vseq + 1;

insert into order_items
values(vorder,vseq,’B002′,25,35.50,5.50);

The reason why I added the vseq variable was to show you that it can also be done. Another thing you can do while you are still connected on the same session you can issue : select vorder from sysibm.sysdummy1   to retrieve the value of the variable.

The variable is dropped when the connection or session is terminated. However its a good practice to drop the variable at the end of the transactions :

DROP VARIABLE vorder;

You can use the same approach if you are using the sequences

The post Inserting child table with parent table having generated column appeared first on techs@work.