Macro to generate Mload Script

DROP MACRO MLOAD_EXPORT
REPLACE MACRO MLOAD_EXPORT
(
DB VARCHAR(100),
TB VARCHAR(100),
PATH VARCHAR(100),
FS VARCHAR(100)
)
AS
(

SEL
1,CAST(1 AS BIGINT),
CAST('.DATEFORM ANSIDATE;
.LOGON server/username,password;
.LOGTABLE USR_WORK.ML_UMS_' || :TB || '_1;
DROP TABLE USR_WORK.WT_UMS_' || :TB || '_1;
DROP TABLE USR_WORK.ET_UMS_' || :TB || '_1;
DROP TABLE USR_WORK.UV_UMS_' || :TB || '_1;
.ROUTE MESSAGES WITH ECHO TO FILE ''' || :PATH || '.ldrlog'' ;
.BEGIN IMPORT MLOAD TABLES ' || :DB || '.' || :TB || '
WORKTABLES USR_WORK.WT_UMS_' || :TB || '_1
ERRORTABLES USR_WORK.ET_UMS_' || :TB || '_1 USR_WORK.UV_UMS_' || :TB || '_1
ERRLIMIT          0
CHECKPOINT     100000
TENACITY 10000
SESSIONS        10
SLEEP 6
;
.LAYOUT InputFileLayout;
' AS VARCHAR(6000))
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE=DATE
UNION
SEL
2,CAST(COLUMNID AS BIGINT),
CAST('.FIELD ' || TRIM(COLUMNNAME ) || '* VARCHAR(255);' AS VARCHAR(6000))
FROM DBC.COLUMNS
WHERE DATABASENAME='' || :DB || ''
AND TABLENAME ='' || :TB || ''
UNION
SEL 3,CAST(3 AS BIGINT), CAST('.DML LABEL TAGDML ;

INSERT INTO ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) ||
'(' AS VARCHAR(6000))
FROM DBC.TABLES
WHERE DATABASENAME='' || :DB || ''
AND TABLENAME ='' || :TB || ''

UNION
SEL 4,CAST(COLUMNID AS BIGINT),CASE WHEN COLUMNID= MAXID THEN COLUMNNAME ELSE COLUMNNAME || ',' END AS DETAILS
FROM DBC.COLUMNS A
JOIN
(
SEL MAX(COLUMNID) AS MAXID,TABLENAME AS TBN ,DATABASENAME AS DBN   FROM DBC.COLUMNS  GROUP BY 2,3
) M
ON A.TABLENAME =M.TBN
AND A.DATABASENAME=M.DBN
WHERE DATABASENAME='' || :DB || ''
AND TABLENAME ='' || :TB || ''

UNION
SEL 5, CAST(5 AS BIGINT), CAST(') VALUES
(' AS VARCHAR(6000))
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE=DATE
UNION
SEL 5,CAST(COLUMNID AS BIGINT), ':' || CASE WHEN COLUMNID= MAXID THEN COLUMNNAME ELSE COLUMNNAME || ',' END AS DETAILS
FROM DBC.COLUMNS A
JOIN
(
SEL MAX(COLUMNID) AS MAXID,TABLENAME AS TBN ,DATABASENAME AS DBN   FROM DBC.COLUMNS  GROUP BY 2,3
) M
ON A.TABLENAME =M.TBN
AND A.DATABASENAME=M.DBN
WHERE DATABASENAME='' || :DB || ''
AND TABLENAME ='' || :TB || ''

UNION
SEL 6,CAST(1 AS BIGINT),
CAST(');
.import infile '''|| :PATH || '''
layout InputFileLayout
 Format vartext''' || :FS || '''
 Apply tagDML
;
.end mload ;
.logoff ;' AS VARCHAR(6000))
FROM SYS_CALENDAR.CALENDAR
WHERE CALENDAR_DATE=DATE
ORDER BY 1,2;
) ;

EXEC MLOAD_EXPORT ('Database, 'Fact', ' filepath', ',');