Two Backslash Unload Error Load Mainframe

Posted in 2014

Topics: Storage & Space Management, Server Administration, Migration, Import/Export & Data Conversion

LONDONMS LONDONMS — Fri, 30 May 2014 22:07:51 — source: IIUG Forums & Mailing Lists

I have to unload around 50 million data from informix, the unload appears two backslash in the field of char not null. ==== ==== ==== CREATE TABLE 'dba'.test_unload ( DATA date NOT NULL, DBSPACE_NAME CHAR(18) NOT NULL ); ==== ==== ==== Insert - One Backslash insert into entire:'dba'.test_unload_entire_table values ('18/06/2014','999999999999990\\\\99'); insert into entire:'dba'.test_unload_entire_table values ('17/06/2014','123451234567890\\\\12');==== ==== ==== SELECT * from 'dba'.test_unload data dbspace_name 18/06/2014 999999999999990\\\\99 17/06/2014 123451234567890\\\\12 ==== ==== ==== unload two Backslash 18/06/2014|999999999999990\\\\\\\\99| 17/06/2014|123451234567890\\\\\\\\12| ==== ==== ==== Hence these two backslash, i want to unload 1 backslash. I'm doing a Informix unload to load the Mainframe - Not permanent but temporary thing. And this second backslash is giving error to load the Mainframe;

↪ replying to LONDONMS LONDONMS

Art Kagel — Fri, 30 May 2014 22:19:30 — source: IIUG Forums & Mailing Lists

Just pass the unload file through this sed filter: sed 's/\\\\\\\\\\\\\\\\/\\\\/g' Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, May 30, 2014 at 6:07 PM, LONDONMS LONDONMS <londonms@gmail.com> wrote: > I have to unload around 50 million data from informix, the unload appears > two > backslash in the field of char not null. > ==== > ==== > ==== > CREATE TABLE 'dba'.test_unload ( > DATA date NOT NULL, > DBSPACE_NAME CHAR(18) NOT NULL > ); > ==== > ==== > ==== > Insert - One Backslash > > insert into entire:'dba'.test_unload_entire_table values > ('18/06/2014','999999999999990\\\\99'); > insert into entire:'dba'.test_unload_entire_table values > ('17/06/2014','123451234567890\\\\12');> ==== > ==== > ==== > SELECT * from 'dba'.test_unload> > data dbspace_name > > 18/06/2014 999999999999990\\\\99 > 17/06/2014 123451234567890\\\\12 > ==== > ==== > ==== > unload two Backslash > > 18/06/2014|999999999999990\\\\\\\\99| > 17/06/2014|123451234567890\\\\\\\\12| > ==== > ==== > ==== > Hence these two backslash, i want to unload 1 backslash. > > I'm doing a Informix unload to load the Mainframe - Not permanent but > temporary thing. And this second backslash is giving error to load the > Mainframe; > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01493e1c25083704faa57062

↪ replying to Art Kagel

Kern Doe — Fri, 30 May 2014 22:53:23 — source: IIUG Forums & Mailing Lists

Keep in mind that besides the problem with 2 slashes (interesting! never seen this before!), you may also have performance problem since you'll be doing 50 mils to a file using "unload" statement. SO why not taking advantage of external table! I tried the output file contains one slash. try this ....it will be super fast, don't believe me? ask Art ! :)) CREATE EXTERNAL TABLE ext_test_unload ( DATA date NOT NULL, DBSPACE_NAME CHAR(18) NOT NULL ) USING ( DATAFILES('DISK:/tmp/ext_test_unload.unl'), FORMAT 'DELIMITED', DELIMITER '|', RECORDEND '', Deluxe, NUMROWS 50, MAXERRORS 50, REJECTFILE '' ); INSERT INTO ext_test_unload SELECT * FROM test_unload; DROP TABLE ext_test_unload; Let's go Green This email contains 100% recycled electrons. ________________________________ From: Art Kagel <art.kagel@gmail.com> To: ids@iiug.org Sent: Friday, May 30, 2014 6:19 PM Subject: Re: Two Backslash Unload Error Load Mainframe [33130] Just pass the unload file through this sed filter: sed 's/\\\\\\\\\\\\\\\\/\\\\/g' Art Art S. Kagel, Principal Consultant ASK Database Management Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, May 30, 2014 at 6:07 PM, LONDONMS LONDONMS <londonms@gmail.com> wrote: > I have to unload around 50 million data from informix, the unload appears > two > backslash in the field of char not null. > ==== > ==== > ==== > CREATE TABLE 'dba'.test_unload ( > DATA date NOT NULL, > DBSPACE_NAME CHAR(18) NOT NULL > ); > ==== > ==== > ==== > Insert - One Backslash > > insert into entire:'dba'.test_unload_entire_table values > ('18/06/2014','999999999999990\\\\99'); > insert into entire:'dba'.test_unload_entire_table values > ('17/06/2014','123451234567890\\\\12');> ==== > ==== > ==== > SELECT * from 'dba'.test_unload> > data dbspace_name > > 18/06/2014 999999999999990\\\\99 > 17/06/2014 123451234567890\\\\12 > ==== > ==== > ==== > unload two Backslash > > 18/06/2014|999999999999990\\\\\\\\99| > 17/06/2014|123451234567890\\\\\\\\12| > ==== > ==== > ==== > Hence these two backslash, i want to unload 1 backslash. > > I'm doing a Informix unload to load the Mainframe - Not permanent but > temporary thing. And this second backslash is giving error to load the > Mainframe; > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01493e1c25083704faa57062 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Kern Doe

LONDONMS LONDONMS — Sat, 31 May 2014 13:01:11 — source: IIUG Forums & Mailing Lists

External Table resolved, thanks for support Kern Doe.

Glossary

dbspace