As a DB2 DBA or as data integrator we all face a challenge in loading large amount of data into DB2 database while up keeping the 24×7 availability of the tables. \u00c2\u00a0Traditionally we use LOAD or sometimes IMPORT while loading large amount of data to tables. Either of them has data concurrency and availability issue. While INSERTS gives the highest availability, it can be painfully slow when inserting millions of rows.<\/p>\n
Brief Intro<\/em> :<\/em><\/span><\/p>\n INGEST from DB2 10 is fast and it enables us to continuously \u00c2\u00a0load \u00c2\u00a0data 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. \u00c2\u00a0Unlike LOAD and IMPORT, \u00c2\u00a0INGEST 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 \u00c2\u00a0production stream. \u00c2\u00a0Another 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 \u00c2\u00a0contain \u00c2\u00a0updates to existing rows .<\/p>\n How does it work ?<\/em><\/span><\/p>\n INGEST has three threads\/parts :<\/p>\n 1. Transporter : \u00c2\u00a0This 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.<\/p>\n 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.\u00c2\u00a0The number of formatter threads is specified by the\u00c2\u00a0num_formatters\u00c2\u00a0configuration parameter.<\/p>\n 3. Flusher: \u00c2\u00a0Flusher 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. \u00c2\u00a0There could be multiple flushers running to increase the throughput of the data ingest.\u00c2\u00a0The number of flushers for each partition is specified by the num_flushers_per_partition\u00c2\u00a0configuration parameter.<\/p>\n Like I said its completely designed to be run efficiently and give high availability.<\/p>\n Can it recover from a failure ?<\/em><\/span><\/p>\n 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. \u00c2\u00a0You 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.\u00c2\u00a0Unlike with the\u00c2\u00a0TERMINATE\u00c2\u00a0parameter of the\u00c2\u00a0LOAD\u00c2\u00a0command, data already committed by the failed INGEST\u00c2\u00a0command is not removed from the table.<\/p>\n What all format can it handle ?<\/em><\/span><\/p>\n INGEST\u00c2\u00a0utlitiy \u00c2\u00a0supports the following input data formats:<\/p>\n Delimited text How about performance ?<\/em><\/span><\/p>\n Obviously this is one of the most sacred part for the DBA when choosing the option to load lot of data. \u00c2\u00a0The 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.<\/p>\n How is it run ?<\/em><\/span><\/p>\n Assume we have a coma delimited customer file as follows that needs to be loaded :<\/p>\n 10001,”Edward”,”Williams”,”12349 West Ave St”,”Taos”,”NM”,”564-573-4839″ The customer table is : Lets see our options to INGEST the data:<\/p>\n This is the simplest form : 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:<\/p>\n ingest from file customer.del format delimited<\/span> The exception table should be in place before you run this. You can create the table with : If your data contains updates to\u00c2\u00a0existing\u00c2\u00a0rows as well as new rows, you can use MERGE statement on your INGEST :<\/p>\n ingest from file customer_monday.del, customer_tuesday.del format delimited<\/span> Note that in the above example, I have two input files. \u00c2\u00a0I 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 ?<\/p>\n If you have a list of rows that you want to delete, then you can do as well :<\/p>\n ingest from file customer1.del format delimited<\/span> How do we restart ?<\/em><\/span><\/p>\n You can restart a terminated INGEST with the RESTART CONTINUE <job_id> option<\/p>\n If you want the ability to easily restart a terminated INGEST use the option RESTART NEW <job_id> on your original INGEST command like :<\/p>\n ingest from file customer_monday.del, customer_tuesday.del format delimited<\/span> 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<\/p>\n ingest from file customer_monday.del, customer_tuesday.del format delimited<\/span> The RESTART CONTINUE option will enable us to restart INGEST from the last commit point.<\/p>\n If you want to terminate the utility and want to clean up from the previously failed run, then you can use \u00c2\u00a0RESTART TERMINATE <job_id> option just like the RESTART CONTINUE option.<\/p>\n How can I monitor the utility ?<\/em><\/span><\/p>\n There are few ways to watch what INGEST utility is doing . \u00c2\u00a0IBM has provided few options:<\/p>\n 1. INGEST GET STATS<\/span> Conclusion<\/em><\/span><\/p>\n 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. \u00c2\u00a0I hate to use large IN clause in my delete statements or have to load the key into a temporary table\u00c2\u00a0scenario. 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 !<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" As a DB2 DBA or as data integrator we all face a challenge in loading large amount of data into DB2 database while up keeping the 24×7 availability of the tables. \u00c2\u00a0Traditionally we use LOAD or sometimes IMPORT while loading large amount of data to tables. Either of them has data concurrency and availability issue. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[303,3,358,275],"tags":[849,722,846,717,782,177,854,853,852,845,847,848,850,851],"class_list":["post-1388","post","type-post","status-publish","format-standard","hentry","category-database","category-db2","category-how-to","category-unix","tag-data-load","tag-db2-import","tag-db2-ingest","tag-db2-load","tag-db2-v10-features","tag-etl","tag-high-performance-data-load","tag-high-performance-load","tag-import","tag-ingest","tag-load-data-with-high-availability","tag-loading-high-volume-of-data","tag-mass-data-insert","tag-restart"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1388","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1388"}],"version-history":[{"count":17,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1388\/revisions"}],"predecessor-version":[{"id":1392,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1388\/revisions\/1392"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1388"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1388"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1388"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nPositional text and binary
\nColumns in various orders and formats<\/p>\n
\n10002,”James”,”Miller”,”43348 Knight St”,”Dumont”,”NJ”,”201-873-4239″
\n10003,”Jessica”,”Robertson”,”489 River N Rd”,”Austin”,”TX”,”512-534-9859″
\n: \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0: \u00c2\u00a0 \u00c2\u00a0 : \u00c2\u00a0 \u00c2\u00a0: \u00c2\u00a0 \u00c2\u00a0: \u00c2\u00a0 : \u00c2\u00a0 : \u00c2\u00a0 \u00c2\u00a0: \u00c2\u00a0 \u00c2\u00a0: \u00c2\u00a0 : \u00c2\u00a0 \u00c2\u00a0: \u00c2\u00a0\u00c2\u00a0: \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0: \u00c2\u00a0 \u00c2\u00a0 : \u00c2\u00a0 \u00c2\u00a0: \u00c2\u00a0 \u00c2\u00a0: \u00c2\u00a0 : \u00c2\u00a0 : \u00c2\u00a0 \u00c2\u00a0: \u00c2\u00a0 \u00c2\u00a0: \u00c2\u00a0 : \u00c2\u00a0 \u00c2\u00a0: \u00c2\u00a0: \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0: \u00c2\u00a0 \u00c2\u00a0 : \u00c2\u00a0 \u00c2\u00a0:
\n90000,”Kathy”,”Rodriguez”,”8293 Brooksheild Apt, # 201″,”Anahiem”,”CA”,”714-354-775<\/p>\n
\nCNumber \u00c2\u00a0 INT \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0—> primary key
\nFName \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 varchar(50)
\nLName \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 varchar(50)
\nStreet \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 varchar(100)
\nCity \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 varchar(50)
\nState \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 \u00c2\u00a0 char(2)
\nTelephone \u00c2\u00a0char(12)<\/p>\n
\ningest from file customer.del format delimited insert into raju.customer;<\/span><\/p>\n
\n ($cid integer external<\/span>
\n ,$firstname char(50)<\/span>
\n ,$lastname char(50)<\/span>
\n ,$streetname char(100)<\/span>
\n ,$cityname char(50)<\/span>
\n ,$statename char(2)<\/span>
\n ,$telephone char(12)<\/span>
\n )<\/span>
\n dumpfile customer_dump.del<\/span>
\n exception table raju.customerexcept<\/span>
\n warningcount 100<\/span>
\n messages ingest_customer.msg<\/span>
\n insert into raju.customer(cnumber,fname,lname,street,city,state,telephone)<\/span>
\n values($cid,<\/span>$lastname,$firstname<\/strong>,$streetname,$cityname,$statename,$telephone);<\/span><\/p>\n
\ncreate table raju.customerexcept like raju.customer \u00c2\u00a0or run the same ddl you used for the customer table.<\/p>\n
\n ($cid integer external<\/span>
\n ,$firstname char(50)<\/span>
\n ,$lastname char(50)<\/span>
\n ,$streetname char(100)<\/span>
\n ,$cityname char(50)<\/span>
\n ,$statename char(2)<\/span>
\n ,$telephone char(12)<\/span>
\n )<\/span>
\n dumpfile customer_dump.del<\/span>
\n warningcount 100<\/span>
\n messages ingest4.msg<\/span>
\n merge into raju.customer on (cnumber = $cid)<\/span>
\n when not matched then<\/span>
\n insert (cnumber,fname,lname,street,city,state,telephone)<\/span>
\n values($cid,$firstname,$lastname,$streetname,$cityname,$statename,$telephone)<\/span>
\n when matched then<\/span>
\n update set (fname,lname,street,city,state,telephone) =<\/span>
\n ($firstname,$lastname,$streetname,$cityname,$statename,$telephone);<\/span><\/p>\n
\n ($cid integer external<\/span>
\n ,$firstname char(50)<\/span>
\n ,$lastname char(50)<\/span>
\n ,$streetname char(100)<\/span>
\n ,$cityname char(50)<\/span>
\n ,$statename char(2)<\/span>
\n ,$telephone char(12)<\/span>
\n )<\/span>
\n delete from raju.customer<\/span>
\n where cnumber = $cid;<\/span><\/p>\n
\n ($cid integer external<\/span>
\n ,$firstname char(50)<\/span>
\n ,$lastname char(50)<\/span>
\n ,$streetname char(100)<\/span>
\n ,$cityname char(50)<\/span>
\n ,$statename char(2)<\/span>
\n ,$telephone char(12)<\/span>
\n )<\/span>
\n dumpfile customer_dump.del<\/span>
\n warningcount 100<\/span>
\n messages ingest4.msg<\/span>
\n restart new ‘customeringest1’<\/strong><\/span>
\n merge into raju.customer on (cnumber = $cid)<\/span>
\n when not matched then<\/span>
\n insert (cnumber,fname,lname,street,city,state,telephone)<\/span>
\n values($cid,$firstname,$lastname,$streetname,$cityname,$statename,$telephone)<\/span>
\n when matched then<\/span>
\n update set (fname,lname,street,city,state,telephone) =<\/span>
\n ($firstname,$lastname,$streetname,$cityname,$statename,$telephone);<\/span><\/p>\n
\n ($cid integer external<\/span>
\n ,$firstname char(50)<\/span>
\n ,$lastname char(50)<\/span>
\n ,$streetname char(100)<\/span>
\n ,$cityname char(50)<\/span>
\n ,$statename char(2)<\/span>
\n ,$telephone char(12)<\/span>
\n )<\/span>
\n dumpfile customer_dump.del<\/span>
\n warningcount 100<\/span>
\n messages ingest4.msg<\/span>
\n restart continue ‘customeringest1’<\/strong><\/span>
\n merge into raju.customer on (cnumber = $cid)<\/span>
\n when not matched then<\/span>
\n insert (cnumber,fname,lname,street,city,state,telephone)<\/span>
\n values($cid,$firstname,$lastname,$streetname,$cityname,$statename,$telephone)<\/span>
\n when matched then<\/span>
\n update set (fname,lname,street,city,state,telephone) =<\/span>
\n ($firstname,$lastname,$streetname,$cityname,$statename,$telephone);<\/span><\/p>\n
\n2. INGEST LIST<\/span> or
\n3 you can query TABLE(MON_GET_CONNECTION(NULL,NULL))<\/span> and look for the application_name DB2_INGEST<\/p>\n