Archiving my JDE data off an AS/400 back tables to CSV from a 400

Let's be honest, if you are running on a 400, your time is running out.  We have a number of very efficient platform migration mechanisms to get you on a more modern platform - and perhaps one that can move to the cloud.

I do like the AS/400, I get on that thing and can do all the admin tasks I need - but it's done.  I have a client that wants to move all their AS/400 data to some "datalake" and then farm it with custom queries.  I do NOT recommend this as an approach, as you will spend all of your time reverse engineering the data and logic to understand what you had.

I recommend putting the data on ice, in a database that you've probably paid for...  then if you need to access or search the data, use the familiar JDE interface.  Cloud is perfect for this, your footprint in terms of $$ is miniscule and you can bring the system up - as and when you need.  This is the perfect choice for archival or retuirement.

Anyway, back to the post.  This is for when I do it next time.

What I will tell you about the script below, is that it works - yay

Save it as a .vbs file (mine is called downloadDataPRODDTA) and run with

>wscript.exe /h:cscript //E:vbscript downloadDataPRODDTA.vbs

The thing about this is that it was running on an old server (oh, did I mention the AS/400)?  The old server.

Note that my script is a little more complex, in terms of the files that it uses for t he download, as it is only grabbing the tables with data, this is easy - you can search my blog for how to create the datacounts file.

drop table proddta.rowcounts;

commit;

create table proddta.rowcounts (table_name varchar (120), AS400_count integer, oracle_count integer) ;

insert into proddta.rowcounts (
select table_name, 0, 0
from qsys2.systables  t1
where table_schema = 'PRODDTA');

''Run the results of this

select 'UPDATE PRODDTA.ROWCOUNTS set AS400_count = ( SELECT COUNT(1) FROM ' || 'PRODDTA.' || table_name || ' ' || ') WHERE table_name = ''' || table_name || ''';' 
from qsys2.systables t1
where table_schema = 'PRODDTA' ;

The following will give you a nice summary 

select table_name, simd, AS400_count
from proddta.rowcounts, ol7333.f9860
where ltrim(rtrim(siobnm)) = ltrim(rtrim(table_name))
and AS400_count > 0 order by AS400_count desc

Now you are ready to start the export of tables that have rows.  Note that some have blobs and they do not behave so they have also been excluded. 

1.5GB is taking about 15 minutes on this old dog - so it's doing okay.

    schema = "PRODDTA"

    server = "JDE400"

    connectString = "Driver={IBM i Access ODBC Driver};System=JDE400;Data Source=Business Data - PROD;User Id=JDE;Password=jde;"

    dim dbConnIn

    set dbConnIn = wscript.CreateObject("ADODB.Connection")

    dbConnIn.CommandTimeout = 60000

    dbConnIn.Open(connectString)

    dim fso

    set fso = wscript.CreateObject("Scripting.FileSystemObject")

        Dim tmp

        set rs = wscript.CreateObject("ADODB.RecordSet")

        rs.Open "SELECT T1.TABLE_NAME FROM QSYS2/SYSTABLES T1, PRODDTA/ROWCOUNTS T2, OL7333/F9860 T3 WHERE T1.TABLE_NAME = T2.TABLE_NAME AND ltrim(rtrim(t1.table_name)) = ltrim(rtrim(t3.siobnm)) AND  T2.AS400_count > 0 AND T1.TABLE_SCHEMA = '" & schema & "' AND t2.table_name not in ('F99410', 'F09505', 'F03B525') ORDER BY AS400_count asc", dbConnIn

        rs.MoveFirst

    tableName = ""

    

        For Each objField In rs.fields

            While objField > "" 

                tableName = objField.Value

                folder = "E:\Myriad\datadump\" & schema & ""

                If Not fso.FolderExists(folder) Then

                    fso.CreateFolder(folder)

                End If

        'wscript.echo schema & "." & tableName

                Call getData(schema, tableName, dbConnIn, folder)

                rs.MoveNext

            WEND

        Next

        rs.close

FUNCTION getData(schema, tableName , dbConnIn , folder)

        If Not fso.FileExists("E:\Myriad\datadump\" & schema & "\" & tableName & ".csv") Then

            set rs1 = wscript.CreateObject("ADODB.RecordSet")

            rs1.Open "Select * From " & tableName, dbConnIn

            set fso = wscript.CreateObject("Scripting.FileSystemObject")

            set ts = fso.OpenTextFile("E:\Myriad\datadump\" & schema & "\" & tableName & ".csv", 2, True)

            line = ""

            For Each tmp2 In rs1.Fields

                line = line & tmp2.Name & ","

            Next

            ts.WriteLine(Left(line, Len(line) - 1))

            While Not rs1.EOF

                line = ""

                For Each tmp1 In rs1.Fields

                    line = line & """" & Replace(tmp1.Value, """", """""") & ""","

                Next

                ts.WriteLine(Left(line, Len(line) - 1))

                rs1.MoveNext

            wend

            rs1.close

            ts.close

        End If

End function