Create an Excel-File from Adabas-Report (AdaRep)

see the documentation in doc/adarep2excel.htm

code is running on unix and windows as well.

comments are welcome.
sos-adarep2excel(20060913-154342).zip (103 KB)

:shock: Interesting, a Java program posted into the Natural open source area :smiley:

Just mentioning it so people don’t download it and expect to load the source into Natural Studio.

yes, it is written in Java. Sorry for that, but it’s a little bit complex to create a BIFF8-Format-File (Excel) with Natural.

For us it’s very useful to have this report in excel for dba’s and developers, too. that’s why i think it might be useful for other natural/adabas dba’s/developers.

I did not test it, but i think, it should be running on every platform, on which Java (and Adabas) is available.

You could use open systems Natural to create an Excel file, there is an example of working with Excel files posted earlier.
The main difference would be that Excel needs to be installed on the same machine as the Natural program since it just does API calls and probably takes more time.
On the other hand, parsing the ADAREP can be done very easily in Natural where it’s a bit more difficult in Java.

I think you are absolutely correct, it is useful to have the Adarep in Excel format, and I really like to see the tools & utilities that others create, I appreciate you sharing it! :smiley:

yes, you are right. it is possible to create excel-files using the object-model of excel (via excel.tlb) and the com-functionality of Natural (for Windows). but: 1) it is only available on Windows. 2) you must have at least an excel.tlb installed and 3) imho its very hard to code with natural, because you don’t have “intelli-sense” :slight_smile:

The Java-solution is running on Unix without windows and excel. (the excel-file is published by an apache-server on the unix-system).

of course is it possible to parse the AdaRep in Natural. Below is a solution. It is not a class-approach. That’s why it is not comparable to the Java-Solution.


* * <docu>
* <project> PPS </project>
* <name>    CADAFL00 </name>
* <type></type>
* <tac></tac>
* <jobname></jobname>
* <title>   Adabas-Utilies: AdaReport-Parser
* </title>
* <description>
* <para>
* This program scans an Adabas-Report (created using the keyword "summary"
* and "contents")
* </para>
* <para>
* The Layout of the AdaRep-Listings is different between the Adabas-Version
* <= 3.3 and >= 5.x.
* This Parser checks the Adabas-Version and analyse the report 
* according to 
* the Adabas-Version.
* </para>
* </description>
* <keywords>
* <keyword>AdaRep</keyword>
* </keywords>
* <categories>
* <category>Utility</category>
* <category>Adabas</category>
* <category>Parser</category>
* </categories>
* <date>    2006-09-21 -
*           (c) 21.09.2006 by sglo111 </date>
* <author>  sglo111 </author>
* <changes>
* <change who="sglo111" when="2006-09-21">
*   <who>sglo111</who>
*   <when>2006-09-21</when>
*   <what>
*   created
*   </what>
* </change>
* </changes>
* </docu>
* Define Data
local using oADADbas    /* ADABAS-Database(-Report)
local
1 #Buffer (a132)
1 Redefine #Buffer
  2 #Allocations (a80)
  2 Redefine #Allocations
    3 #Filler1 (a1)
    3 #strContainerFile (a10)
    3 #strDeviceType (a6)
    3 #intExtentsFrom (a11)
    3 #intExtentsTo (a12)
    3 #intNumberOfBlocks (a13)
    3 #intBlockSize (a11)
    3 #intTotalSize (a15)
*
1 Redefine #Buffer
  2 #ExtentsAndPadding (a80)    /* Block 2
  2 Redefine #ExtentsAndPadding
    3 #FileNo (n4)
    3 Filler01 (a1)
    3 #FileName (a16)
    3 #strLoadDate (a12)
    3 #strTopISN (a14)
    3 #intIndexLevel (N8)
    3 #intExtentsNI (N3)
    3 #intExtentsUI  (n3)
    3 #intExtentsA  (n3)
    3 #intExtentsD   (n3)
    3 #intPadA (n3)
    3 #intPadD  (n3)
    3 #strFlags (a4)
*
1 Redefine #Buffer
  2 #FileAllocation (a80)  /* Block 3
  2 Redefine #FileAllocation
    3 #FileNo2 (n4)
    3 Filler11 (a1)
    3 #FileName2 (a16)
    3 #AllocatedBlocksNI (n8)
    3 #AllocatedBlocksUI (n7)
    3 #AllocatedBlocksAC (n7)
    3 #AllocatedBlocksDS (n10)
    3 #UnusedBlocksNI (n9)
    3 #UnusedBlocksUI (n7)
    3 #UnusedBlocksDS (n10)
*
1 #intWhichPart (i2)
1 #flgIsSeparator (l)
1 #isBlockActive (l)
1 #intTopISN (N10)
1 #strA (a30/1:99)
1 #i (i4)
1 #flgIsV5 (l)   /* Report-layout 4 AdabasVersion >= 5
1 #flgLogRecords (l) init <true>
end-define
*
format ps=60 ls=132
Define work File 01 '/tmp/adarep.txt' Type 'ASCII-COMPRESSED'
* fine work File 01 '/tmp/adarep-v3.txt' Type 'ASCII-COMPRESSED'
*
read work file 1 #Buffer
  if #isBlockActive Then
    Perform CheckSeparator
    if #flgIsSeparator then
      #isBlockActive := false
      escape top  /* read next record
    end-if
*
    Decide on first Value of #intWhichPart
      value 0
        Perform ProcessLineOfBlock0
*
      value 1  /* Process SizeTable
        if #Buffer = ' ' Then
          escape Top   /* read next record
        end-if
        Perform ProcessLineOfBlock1
      value 2 /* Extents-Table
        if #Buffer ne ' ' then  /* end of block 2 not marked by dashed-line
          Perform ProcessLineOfBlock2
        else
          #isBlockActive := false
          escape top   /* read next record
        end-if
*
      value 3 /* File-Allocation-Block (V5: allocated Blocks)
        Perform ProcessLineOfBlock3
*
      value 4 /* V5 only: unused Blocks
        Perform ProcessLineOfBlock4
      none value
        ignore
    end-decide
  else
    Perform CheckSeparator
    if #flgIsSeparator then
      add 1 to #intWhichPart
      #isBlockActive := true
      escape top
    end-if
    if #intWhichPart = 0 Then
      Perform ProcessLineOfBlock0
    end-if  /* #intWhichPart = 0
  end-if
end-work
Define Subroutine CheckSeparator
if substr(#Buffer, 1, 10) = '----------' Then
  #flgIsSeparator := True
else
  #flgIsSeparator := False
end-if
End-Subroutine /* CheckSeparator
Define Subroutine WriteBuffer
*  write #intWhichPart #buffer (al=80)
ignore
End-Subroutine /* Write-Buffer
*
Define Subroutine ProcessLineOfBlock0
separate #Buffer left justified into #strA(*) with delimiters '$, $'
giving number in #i
/* write #i
Decide for first condition
  when substr(#Buffer,1,17) = '%ADAREP-I-STARTED'
    #strAdaVersion := #strA(05)
    if substr(#strAdaVersion, 1, 2) = '5.' Then
      #flgIsV5 := True
    end-if
    #strReportDate := #strA(2)
    #strReportTime := #strA(3)
  when substr(#Buffer,1, 13) = 'DATABASE NAME'
    #strDatabaseName := #strA(3)
  when substr(#Buffer,1, 11) = 'DATABASE ID'
    #lngDatabaseID := val(#strA(3))
  when substr(#Buffer,1, 23) = 'MAXIMUM NUMBER OF FILES'
    #lngMaximumNumberOfFiles := val(#strA(5))
  when substr(#Buffer,1, 19) = 'ACTUAL FILES LOADED'
    #lngActualNoOfFilesLoaded := val(#strA(4))
  when none
    ignore
end-Decide
end-Subroutine  /* ProcessLineOfBlock
Define Subroutine ProcessLineOfBlock1
if #flgLogRecords Then
  write #intWhichPart
    #strContainerFile
    #strDeviceType
    #intExtentsFrom
    #intExtentsTo
    #intNumberOfBlocks
    #intBlockSize
    #intTotalSize
end-if
end-Subroutine  /* ProcessLineOfBlock1
*
Define Subroutine ProcessLineOfBlock2
*
separate #Buffer left justified into #strA(*) with delimiters '$ $'
giving number in #i
#FileNo := val(#strA(1))
#FileName := #strA(2)
#strLoadDate := #strA(3)
#strTopISN := #strA(4)
#intIndexLevel := val(#strA(5))
#intExtentsNI := val(#strA(6))
#intExtentsUI := val(#strA(7))
#intExtentsA := val(#strA(8))
#intExtentsD := val(#strA(9))
#intPadA := val(#strA(10))
#intPadD := val(#strA(11))
#strFlags := #strA(12)
examine #strTopISN for ',' and delete
#intTopISN := val(#strTopISN)
*
if #flgLogRecords Then
  write #intWhichPart
    #FileNo
    #FileName
    #strLoadDate
    #intTopISN
    #intIndexLevel
    #intExtentsNI
    #intExtentsUI
    #intExtentsA
    #intExtentsD
    #intPadA
    #intPadD
    #strFlags
end-if
*
end-Subroutine  /* ProcessLineOfBlock2
Define Subroutine ProcessLineOfBlock3
*
if #flgIsV5 then
  separate #Buffer left justified into #strA(*) with delimiters '$ $'
  giving number in #i
  #FileNo2 := val(#strA(1))
  #FileName2 := #strA(2)
  for #i = 3 to 6
    examine #strA(#i) for ',' and delete
  end-for
  #AllocatedBlocksNI := val(#strA(3))
  #AllocatedBlocksUI := val(#strA(4))
  #AllocatedBlocksAC := val(#strA(5))
  #AllocatedBlocksDS := val(#strA(6))
  if #flgLogRecords Then
    write
      #intWhichPart
      #FileNo2
      #FileName2
      #AllocatedBlocksNI
      #AllocatedBlocksUI
      #AllocatedBlocksAC
      #AllocatedBlocksDS
  end-if
else
  if #flgLogRecords Then
    write
      #intWhichPart
      #FileNo2
      #FileName2
      #AllocatedBlocksNI
      #AllocatedBlocksUI
      #AllocatedBlocksAC
      #AllocatedBlocksDS
      #UnusedBlocksNI
      #UnusedBlocksUI
      #UnusedBlocksDS
  end-if
end-if
end-Subroutine  /* ProcessLineOfBlock3
Define Subroutine ProcessLineOfBlock4
*
if #flgIsV5 then
  separate #Buffer left justified into #strA(*) with delimiters '$ $'
  giving number in #i
  #FileNo2 := val(#strA(1))
  #FileName2 := #strA(2)
  for #i = 3 to 5
    examine #strA(#i) for ',' and delete
  end-for
  #UnusedBlocksNI := val(#strA(3))
  #UnusedBlocksUI := val(#strA(4))
  #UnusedBlocksDS := val(#strA(5))
  if #flgLogRecords Then
    write
      #intWhichPart
      #FileNo2
      #FileName2
      #UnusedBlocksNI
      #UnusedBlocksUI
      #UnusedBlocksDS
  end-if
else
/* Error: Block4 not possible with AdaVers <5
  ignore
end-if
end-Subroutine  /* ProcessLineOfBlock4
end

:idea: With Natural 6.2 you can create, write & read CSV (comma-seperated values) files which can be accessed by EXCEL.

tx for the posting,

it was never a big issue to create csv-files out of natural, that’s not a question of version 6.2.?. we are doing this since 10 years or more, even on ibm-host. this enhancement of natural is imho not so useful …

But: why should i create csv-files, when i can have pure excel?

It would be usefull, because many many other programs and programming languages got a CSV-plugin as well.

See http://en.wikipedia.org/wiki/Comma-separated_values#Programming_language_tools

BTW: Did you implement the “real thing” (i.e. http://tools.ietf.org/html/rfc4180 )?

Yep you can always create any format by assembling it from the pieces. But now it is implemented in Natural. Means you don’t need to assemble it before the WRITE or disassemble it after the read.

DEFINE WORK FILE 1 #NAME TYPE ‘CSV’
WRITE WORK 1 VARIABLE #VALUE1 #VALUE2 #VALUE3

READ WORK 1 #VALUE1 #VALUE2 #VALUE3

A sample program is in SYSEXV(V62WFCSV).

Ok, it is no pure EXCEL but on the other hand it is easy to use :wink:

BTW from mainframe I use Natural/Entire Connection to create EXCEL files.

Not bad! CSV means more than doing one COMPRESS … WITH DELIMITERS … You have to code a proper quotation logic as well. Now SAG is responsible for that :wink:

Lukas: have you ever tried to create a csv-file this way with headings and an alternate separator-character? i use 6.2.2 and i get a core-dump from the natparm-utility (hp-ux ia64) if i try to define properties for CSV. :cry:
btw: why must i change the natparm, if i want to have headers or an alternate sep-char?

This functionality is not on top of my whish-list for “workFiles” in natural. for example: the workfile-number actually is a constant, i dream of a variable, so i can use it as parameter for a subprogram-call. Another wish is a function that gives me a “free”-workfile-number (like in VB). It is not possible to make a workaround for the programmer for this missing functionality. Making “easy things easier” is not helpful. Making “impossible things possible” should be the policy of development or enhancement of natural.

just my 2 cents … :lol:

Matthias: yes, we did implement the “real thing”. :smiley: The CSV-Files are imported by oracle, oo, access, excel and others. Never a problem occurred.

some years ago i wrote a generator (in VB :shock: ), which is able to create on a mouse-click based on an lda/pda/view the natural-code-snippet to create or read csv.

I set header off in the NATPARM and use

WRITE WORK 1 VARIABLE ... 

The “VARIABLE” has the effect that I can use different lines to write, i.e. first I write my own header line and then the other lines.

The separator character can only defined in Natparm. If you get a dump, it looks like a bug :cry: I have heard (but not verified) that there was a problem like this. So maybe install newest version and retry. If this doesn’t help, send a request to Software AG.

On the other hand, to set the separator in the program, specify the work file number as variable and providing functionality to return the first free number sounds all useful. I would recommend that you open a C/E request for it.

Do you know the user exit USR2011N? With this exit you can save the settings of a work file and reset it later. Thus if you have a subprogram which wants to use a work file but does not know which one is free, it can save the settings of any work file, use its number and restore the settings in the end.