Re: [DB2EUG] loading varchar data with method L

[Rita Childress]

oh, I get it, you need to put Y or N for each column on each record in the data file.  Those updates are looking more appealing :)  Thanks!   ----- Original Message ----- From: Rita Childress To: [EMAIL PROTECTED] Sent: Thursday, February 12, 2004 6:18 AM Subject: Re: [DB2EUG] loading varchar data with method L Thanks for your answer Russ.  I forgot to mention that the column does have data in some of the input records.  The way I read null indicators is that they null out the column even if there is data?  I will give the null indicators a try today though in case I am reading the manual wrong. I was hoping to avoid updates as this is a data warehouse and there are a lot of varchar columns.  I am getting 2 TB of data in a couple of weeks. Rita ----- Original Message ----- From: [EMAIL PROTECTED] To: Rita Childress Cc: [EMAIL PROTECTED] Sent: Thursday, February 12, 2004 2:20 AM Subject: Re: [DB2EUG] loading varchar data with method L Rita, there is a real good example of how to do this at this website https://aurora.vcu.edu/db2help/db2dm/frame3.htm#db2dm64 Basicaly you include a set of null indicator columns that have a 'Y' or 'N' to indicate if the column is null (y) or not null (n). db2 load from ascfile1 of asc modified by striptblanks reclen=40       method L (1 20, 21 22, 24 27, 28 31)       null indicators (0,0,23,32)       insert into table1 (col1, col5, col2, col3) the "null indicators" clause above is interpreted as follows: col1 & col5 are columns that are not nullable, hence no null indicator column. col2 will be null if the input record contains a "Y' in position 23. col3 would not be null if position 32 were an 'N'. The null indicator columns can be intermixed as in this example or they can be at the end of the record or any combination. (They must have either a 'Y' or 'N'.) This all assumes you can add these columns to the input. If not, you can load the data and then follow the load with an update statement that sets the column to null if it is blank. (one update statment after the load.) HTH Russ "Rita Childress" <[EMAIL PROTECTED]>@Lugwash.org on 02/11/2004 08:32:00 PM Please respond to "Rita Childress" <[EMAIL PROTECTED]> Sent by:    [EMAIL PROTECTED] To:    <[EMAIL PROTECTED]> cc: Subject:    [DB2EUG] loading varchar data with method L My question is I have a fixed width file which is loading an empty string into my varchar columns when no data is present while I want these to load as null.  I can't find a way to control this load behavior.  Using modified by striptblanks has taken the data down from a x length string for varchar(x) to a zero length string, but this doesn't meet the is null predicate. TIA Rita - :::  When replying to the list, please use 'Reply-All' and make sure :::  a copy goes to the list ([EMAIL PROTECTED]). ***  To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]  ***  For more information, check http://www.db2eug.uni.cc