I use the following code in ksh to create data file that will be transfered by FTP to mainframe:
sqlplus -s x/xxx@xxxxx << END 2> $global_path/log/PrHilanActivities.err
SPOOL /n/ttpafn01/vol/vol1/unix-apps/production/EAI/entirex/fileout/hilan/TLRACT.KLT
SET HEADING OFF FEED ON PAGESIZE 0 space 0 LINESIZE 87
select '122', '0303',
CASE SUBSTR(a.activity_status,1,1) WHEN 'C' THEN '2' ELSE ' ' END upd,
'804',
LPAD(SUBSTR(a.activity_id,2,9),9,'0') a_id, 'p',
LPAD(REVERSE(LOWER(SUBSTR(a.activity_desc,1,29))),29,' ') desc1, ' ' bl,
LPAD(SUBSTR(b.baan_project_id,2,12), 12, '0') proj ,
CASE SUBSTR(b.baan_project_id,1,1) WHEN 'I' THEN '02' ELSE '01' END p_type,
'2',
NVL(SUBSTR(to_char(NVL(a.activity_open_date,b.created_date), 'DDMMYY' ),1,6), '010101') o_date,
NVL(SUBSTR(to_char(a.activity_closed_date, 'DDMMYY' ),1,6), '999999') cl_date
from ats_activity a ,ats_project b
where a.ats_project_id=b.ats_project_id
AND SUBSTR(a.activity_id,1,1) = 'P'
AND a.activity_closed_date is null
AND SUBSTR(a.activity_status,1,1) <> 'C'
AND ( ( SUBSTR(b.baan_project_id,3,4) >= '2000' AND SUBSTR(b.baan_project_id,3,4) <= '3699' )
OR ( SUBSTR(b.baan_project_id,3,4) >= '1104' AND SUBSTR(b.baan_project_id,3,4) <= '1699' ) )
AND (a.activity_open_date > sysdate-10 OR a.activity_closed_date > sysdate-10);
spool off
END
) >> $LOG
It looks OK in UNIX, but when the file arrives to mainframe it looks like one line. What shoud I do to solve the problem.
Thanks a lot.
Best regards
Dani.
