Two Backslash Unload Error Load Mainframe

Posted in 2014

Summary

Londonms found that unloading a CHAR column containing a backslash sequence doubled the backslashes in the file, which broke a mainframe load of about 50 million rows. Art Kagel suggested filtering the unload file through sed to collapse the doubled backslashes. Kern Doe recommended an external table (delimited, deluxe) for speed, which writes a single backslash. Londonms confirmed the external table solved it.

Auto-generated by Claude from the posts below — may be imperfect; read the full thread.

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