DB2 SQL – SPUFI – Update or Insert a value that is more than 72 characters


, , , , , , , , , , ,

We often run batch SPUFI requests to update/insert data into DB2 tables. Some times, some of the values that we need to insert/update can be more than 72 characters. In JCL, we cannot enter the data more than 72 characters, so below are different ways with which we can accomplish this.

1) Code your data till 72 byte and continue the next character at the first column in the next line

2) Update Table set col = ‘first 30 bytes data’;
Update Table set col = substr(col,1,30) || ‘second 30 bytes data’;
Update Table set col = substr(col,1,60) || ‘third 30 bytes data’;

3) Update table set col = ‘initial length’ ||
‘remaining length data’;

If your requirement is just to replace few chars (‘abc’ with ‘def’) in a string that is greater than 72 chars, then you could use just REPLACE option instead of mentioning the complete string

4) Update Table
Set col = replace(col,’abc’,’def’);