How to generate scripts to run an ASCII dump and load for all tables?

How to generate scripts to run an ASCII dump and load for all application tables?

The preferred way to dump and load is by doing a binary dump and load as described in the following article:
How to generate scripts to run binary dump and load for all tables?
as this is faster and easier, for correcting database corruption issues (for example caused by hardware issues) an ASCII dump and load is however sometime needed.
Database corruption can however cause the data dictionary tool used for the ASCII dump process to crash during the dump process, and therefore prevent the dump of the non corrupted tables when more than one table is selected to be ASCII dumped.
 

The scripts generated by the 4GL code below will allow to determine which table(s) causes a crash during the ASCII dump process while still continuing dumping the non corrupted tables.

For the 4GL script to work the following commands will need to be executed first:

cd %DLC%\src
or 
cd $DLC/src 
and then on Windows:
extractpl prodict.pl
extractpl adecomm.pl
and on Linux/Unix:
./extractpl prodict.pl
./extractpl adecomm.pl


The following 4GL code: 


DEFINE VARIABLE dir-name AS CHARACTER NO-UNDO FORMAT "X(25)".
DEFINE VARIABLE db-name AS CHARACTER NO-UNDO.
DEFINE VARIABLE front-end AS CHARACTER NO-UNDO.
DEFINE VARIABLE outstring AS CHARACTER NO-UNDO FORMAT "X(100)".
DEFINE VARIABLE ablCodeString AS CHARACTER NO-UNDO FORMAT "X(100)".
DEFINE VARIABLE table-name AS CHARACTER NO-UNDO.

DEFINE STREAM dumpcmds.
DEFINE STREAM loadcmds.

IF OPSYS = "WIN32" THEN
DO:
OUTPUT STREAM dumpcmds TO asciiDumpScript.bat.
OUTPUT STREAM loadcmds TO asciiLoadScript.bat.

FOR EACH _file WHERE _file-num > 0 AND _file-num < 32768:
ASSIGN outstring = "prowin " + DBNAME + " -S 6789 -H localhost -p DUMP" + _file-name + ".p -b >> " + _file-name + "_log.txt" .
ASSIGN table-name = _file-name .
PUT STREAM dumpcmds outstring SKIP.

ASSIGN outstring = "prowin " + DBNAME + " -S 6789 -H localhost -p LOAD" + _file-name + ".p -b >> " + _file-name + "_log.txt" .
PUT STREAM loadcmds outstring SKIP.

DEFINE STREAM 4glcmds.
OUTPUT STREAM 4glcmds TO value("DUMP" + table-name + ".p") .
ASSIGN ablCodeString = "RUN prodict/dump_d.p (~"" + _file-name + "~",~".~",~"~")." .
PUT STREAM 4glcmds ablCodeString SKIP.
OUTPUT STREAM 4glcmds CLOSE.

DEFINE STREAM 4glcmdsLOAD.
OUTPUT STREAM 4glcmdsLOAD TO value("LOAD" + table-name + ".p") .
ASSIGN ablCodeString = "RUN prodict/load_d.p (~"" + _file-name + "~",~".~")." .
PUT STREAM 4glcmdsLOAD ablCodeString SKIP.
OUTPUT STREAM 4glcmdsLOAD CLOSE.

END.

OUTPUT STREAM dumpcmds CLOSE.

END.


will then generate a DUMPtableName.p for each database table containing something like:
RUN prodict/dump_d.p ("tableName",".","").
and a LOADtableName.p for each database table containing something like:
RUN prodict/load_d.p ("tableName",".").
as well as 2 .bat files asciiDumpScript.bat and asciiLoadScript.bat or asciiDumpScript.sh and asciiLoadScript.sh that will contain lines like:
prowin databaseName -S 6789 -H localhost -p DUMPtableName.p -b >> tableName_log.txt
for the ASCII dump batch script and
prowin databaseName -S 6789 -H localhost -p LOADtableName.p -b >> tableName_log.txt
for the ASCII load batch script.

When data need to be loaded to a specific tenant (in a multi tenant database) then instead of something like this in the existing script described above:
ASSIGN outstring = "prowin " + DBNAME + " -S 6789 -H localhost -p LOAD" + _file-name + ".p -b >> " + _file-name + "_log.txt" .
then something like this would then need to be used to have the ascii load be done with a specific tenant user to its own tenant in the database :
ASSIGN outstring = "prowin " + DBNAME + " -S 6789 -H localhost -p LOAD" + _file-name + ".p -U someUser@someDomain -P theSomeUserPassword -b >> " + _file-name + "_log.txt" .
 

References to Other Documentation:

Progress Article(s):
 How to dump around corrupted records?