Die Anfrage eines Kunden, wie kann man eine Db2-Datei von der IBM i als CSV-Datei ins IFS übertragen und dabei beliebige Spaltenüberschriften wählen, wurde im letzten Artikel mit einem fertigen CL-Programm vorerst teilweise beantwortet.
Im aktuellen Artikel wird die Lösung mit einem flexibleren CL-Programm und einer detaillierten Beschreibung der dazu verwendeten SQL-Features erweitert.
Die zu lösende Aufgabe
Die Übertragung einer kompletten Datei als CSV-Datei ins IFS kann mit dem CL-Befehl CPYTOIMPF erfolgen. Als Überschrift für die einzelnen Spalten kann entweder *SYS oder *SQL ausgewählt werden. Wie kann man auch andere Texte als Spaltenüberschriften verwenden?
Welche Spalten einer Tabelle können auch als Text für die Spaltenüberschrift verwendet werden? Dazu wird eine Tabelle AUFTRAG erstellt:
Quelle: BärHieraus ist zu erkennen, dass zusätzlich zu den normalen Spaltennamen auch Ergänzungen zu diesen existieren: Spaltenkennungen, Spaltenüberschriften und Kommentare.
Mit Hilfe der ab ACS 1.1.9.3 erweiterten Unterstützung für das Einfügen von Zeilen in eine Tabelle werden die folgenden Daten hinzugefügt:
Quelle: BärIn der folgenden Abbildung wird dargestellt, welche Texte bei welchen Features verwendet werden.
Quelle: BärFür die Ausführung mit IBM i ACS kann über die Vorgaben eingestellt werden, welche Spaltenüberschriften angezeigt werden sollen:
Quelle: BärDie Zuordnung der so gewählten Überschriften ist aus folgender Abbildung ersichtlich:
Quelle: BärDer CL-Befehl CPYTOIMPF
Die Übertragung einer Datei ins IFS kann mit dem CL-Befehl CPYTOIMPF erfolgen. Damit besteht die Möglichkeit anzugeben, ob Spaltennamen als Überschrift hinzugefügt werden sollen. Dies wird über den Parameter ADDCOLNAM gesteuert. Folgende Werte sind möglich:
- *NONE – keine Spaltennamen
- *SYS – Die Systemspaltennamen werden hinzugefügt
- *SQL – Die SQL-Spaltennamen werden hinzugefügt
In unserem Fall mit der oben erstellten Datei ergibt das folgende Ergebnisse:
Quelle: Bär
Quelle: BärAus einer vorherigen Abbildung ist zu erkennen, dass die drei Textspalten COLUMN_HEADING, COLUMN_TEXT und LONG_COMMENT mit dem CL-Befehl nicht aktivierbar sind. Wie dies dennoch realisierbar ist, wird im folgenden gezeigt.
Weitere Infos zu CPYTOIMPF:
https://www.ibm.com/docs/en/i/7.3?topic=ssw_ibm_i_73/cl/cpytoimpf.html
https://www.ibm.com/support/pages/cpytoimpf-and-cpyfrmimpf-examples
Der Systemkatalog QSYS2.SYSCOLUMNS
Wie ist es nun aber möglich, die nicht über CPYTOIMPF ansprechbaren Texte für die CSV-Datei, aber in der Datei bzw. Tabelle definierten Texte, als Spaltenüberschriften anzusprechen? Hier hilft ein Systemkatalog: QSYS2.SYSCOLUMNS.
Der Systemkatalog enthält die Namen aller Spalten, die in den Dateien und Tabellen auf der IBM i existieren.
— alle Spalten, die auf der IBM i existieren
select * from qsys2.syscolumns
order by Column_Name, Table_Name, Table_Schema ;
Die Spalten einer Tabelle / Datei liefert folgende SQL-Anweisung:
— alle Spalten einer Tabelle
select * from qsys2.syscolumns
where Table_Name=’AUFTRAG’ and Table_Schema=’TKLLIB’ ;
Diese Liste enthält wiederum Spalten, die für die Lösung unserer Aufgabenstellung verwendet werden können:
— nur die Textspalten einer Tabelle für Spaltenüberschriften
select Column_Name, System_Column_Name, Column_Heading, Column_Text, Long_Comment from qsys2.syscolumns
where Table_Name=’AUFTRAG’ and Table_Schema=’TKLLIB’ ;
Und so ist das Ergebnis:
Quelle: BärAlle vier Spalten stellen das Ergebnis userer Dateidefintion und könnten dann als Spaltenüberschrift verwendet werden. Aber wie könnte das realisiert werden?
Eine Lösung für die Verwendung von COLUMN_HEADING zeigt das folgende Beispiel:
Alle vier Spalten stellen das Ergebnis unserer Dateidefinition und könnten dann als Spaltenüberschrift verwendet werden. Aber wie könnte das realisiert werden?
Eine Lösung für die Verwendung von COLUMN_HEADING zeigt das folgende Beispiel:
— Column_Heading einer Tabelle aus SYSCOLUMNS
select Column_Heading from qsys2.syscolumns
where Table_Name=’AUFTRAG’ and Table_Schema=’TKLLIB’ ;
Quelle: BärWie kann man die so ermittelten Texte aber jetzt als Überschriftszeile und mit entsprechendem Trennzeichen zusammen in eine Zeile ausgeben? Auch hierfür bietet SQL wieder eine Lösung.
Weitere Infos zu QSYS2.SYSCOLUMNS:
https://www.ibm.com/docs/en/i/7.1?topic=views-syscolumns
Die SQL-Aggregat-Funktion LISTAGG
Die Funktion LISTAGG fasst eine Menge von Zeichenelementen in eine Zeichenfolge zusammen, indem die einzelnen Elemente miteinander verbunden werden. Der erste Parameter der Funktion gibt an, welche Elemente zusammengefasst werden sollen. Der zweite Parameter gibt das Zeichen an, mit dem die einzelnen Elemente getrennt werden.
Für unser Beispiel mit den Spaltenüberschriften ist die folgende SQL-Anweisung eine Lösung:
Quelle: BärBei Verwendung von COLUMN_TEXT stellt sich die Zeile wie folgt dar:
Quelle: BärDie einzelnen Spaltennamen sind wie gewünscht durch ein Semikolon (;) getrennt, was die Voraussetzung ist für den Einsatz in einer CSV-Datei.
Und wie kann dieses Konstrukt nun in eine CSV-Datei im IFS übertragen werden? Sie dürfen nur einmal raten: mit SQL.
Weitere Infos zu LISTAGG finden Sie unter:
https://www.ibm.com/docs/en/db2/11.5?topic=functions-listagg
Die SQL Stored Procedure QSYS2.IFS_WRITE
Mit den SQL-Prozeduren IFS_WRITE, IFS_WRITE_BINARY und IFS_WRITE_UTF8 können Daten in eine IFS-Streamfile geschrieben werden. Die Daten können als Zeichen, binär oder als UTF-8 eingetragen werden. Die Daten können bei einer bestehenden Datei entweder hinzugefügt oder ersetzt werden. Falls die zu beschreibende Datei noch nicht existiert, kann diese erstellt werden.
— COLHDG in CSV-Datei schreiben
call qsys2.ifs_write_UTF8(
path_name => ‘/home/TB/xxx.csv’,
line => (select listagg(Column_Heading, ‘;’) from qsys2.syscolumns
where Table_Name=’AUFTRAG’ and Table_Schema=’TKLLIB’
),
overwrite => ‘REPLACE’,
end_of_line => ‘CRLF’
);
Und hier ist das Ergebnis mit Excel:
Quelle: BärWeitere Infos zu QSYS2.IFS_WRITE:
https://www.ibm.com/docs/en/i/7.3?topic=is-ifs-write-ifs-write-binary-ifs-write-utf8-procedures
Und jetzt alles in einem CL-Programm
Mit diesen Erklärungen wurde ein CL-Programm erstellt, welches als Parameter den Namen der zu übertragenden mit Bibliothek und einem Übertragungskennzeichen &UEB, das steuert, ob und welche Art von Spaltenüberschrift verwendet werden soll.
Aus Platzgründen kann hier nur der Hauptteil des Programms dargestellt werden. Sie können ja die einzelnen Subroutinen selbst erstellen. Allerdings sind da noch einige Fallstricke zu beachten. Darüber wird dann im nächsten Artikel berichtet und weiter SQL-Features vorgestellt.
Quelle: BärVerwendete Features
In diesem Artikel wurden die folgenden Features verwendet:
- CREATE OR REPLACE TABLE – Tabelle mit SQL erstellen
- COMMENT ON COLUMN – SQL-Anweisung
- CPYTOIMPF – CL-Befehl
- QSYS2.SYSCOLUMNS – Systemkatalog Spalten
- LISTAGG – SQL Aggregatfunkton
- QSYS2.IFS_WRITE – SQL Stored Procedure
Vorschau:
Im nächsten Artikel werden die noch fehlenden Subroutinen erstellt und erklärt. Dabei werden noch einige Fallstricke zu beachten sein. ZurAnwendung kommen auch weitere SQL-Features.
Bis dahin wünsche ich Ihnen weiterhin viel Spaß beim Vermehren Ihrer Fertigkeiten.
Der Autor Theo Bär schreibt regelmäßig für den TechKnowLetter.
Sie erreichen ihn unter
EDV-Beratung Theo Bär
Ringmauerweg 1
69250 Schönau
Tel.: (+49) 6228 912 630
E-Mail: [info@edv-baer.com]
Sechs Ausgaben des TechKnowLetters erhalten Sie hier für 88 Euro.