I corrected the awk script, so it runs now in Qshell on my iSeries too (I
have V7R1).
Below is the source. To run it on iSeries, be sure to save the source in a
text file, which is of the type UNIX ( i.e. every line should end with LF
- not CR LF like in Windows )
BEGIN OF cbl2sql.awk >>>>>>>>>>>>>>>>>>>>>>>
# Run:
# awk -f cbl2sql.awk <COBOL_CopyBook>
BEGIN {
################## User Settings ####################
# CREATE TABLE statement indikator (0=false, 1 = true)
create_table = 0
######################################################
# setting the conversion rules
pic2sql["X"] = "CHAR"
pic2sql["9"] = "DEC"
# initialize variables
fld_nr = 0
table_name = ""
ERROR_CREATE_TABLE = 0
}
{
# remove EOL characters
chomp()
}
$1 == "" {
# skip empty lines
next
}
$1 == "*" {
# skip comment lines
next
}
$2 == "FILLER" {
# skip FILLER
next
}
# process only lines which begins with level number, i.e. $1 is numeric
$1 + 0 == $1 {
level_number = $1
var_name = $2
gsub(/[-]/,"_",var_name)
picture = $3
data_type = $4
pic = get_picture($4)
if (level_number == 1) {
if (create_table) {
if (table_name == "") {
# print CREATE TABLE statement
table_name = trim(var_name)
gsub(/[.]/,"",table_name)
gsub(/[-]/,"_",table_name)
printf ("create table %s (\n", table_name)
}
else {
# signal error an exit
ERROR_CREATE_TABLE = 1
exit
}
}
}
# processing strings
if (pic == "X") {
string_len = get_string_length(data_type)
if (create_table) {
fld_nr += 1
if (fld_nr > 1) {
# print comma
printf(",\n")
}
}
printf(" %-30s %s(%s)", var_name, pic2sql[pic], string_len)
if (!create_table) {
printf(",\n")
}
}
# processing numbers
if (pic == "9") {
if (data_type ~ /V/) {
# when PIC S9(15)V99 split into list (S9(15), 99)
split(data_type, data_type_lst,"V")
integer_places = get_numeric_length(data_type_lst[1])
decimal_places = get_numeric_length(data_type_lst[2])
number_len = integer_places + decimal_places
}
else {
decimal_places = 0
number_len = get_numeric_length(data_type)
}
if (create_table) {
fld_nr += 1
if (fld_nr > 1) {
printf(",\n")
}
}
printf(" %-30s %s(%s,%s)", var_name,
pic2sql[pic], number_len, decimal_places)
if (!create_table){
printf(",\n")
}
}
}
END {
if (create_table) {
print "\n)\n"
printf("Definition of Table %s with %d Fields was created.\n\n",
table_name, fld_nr)
if (ERROR_CREATE_TABLE) {
print "* ERROR: There are more level = 01 declarations !"
print "* It's inpossible to generate CREATE TABLE statement !\n"
}
}
else {
print "Done."
}
}
# ------------------------------- functions
-----------------------------------
function chomp() {
# strip out the carriage return or line feed at the end of current line
# the function modifies global variable $0 (current line)
sub(/\r$/, "", $0)
sub(/\n$/, "", $0)
}
function trim(fld) {
# remove leading and trailing spaces from field
gsub(/^[ \t]+/,"",fld)
gsub(/[ \t]+$/,"",fld)
return fld
}
function get_picture(datatype) {
my_datatype = toupper(datatype)
if (my_datatype ~ /X/) {
pic = "X"
}
else if (my_datatype ~ /9/){
pic = "9"
}
else {
pic = "UNDEF_DATA_TYPE !"
}
return pic
}
function get_string_length(pic_str) {
my_str = toupper(pic_str)
if (my_str ~ /\([0-9]+\)/) {
# remove all characters X,(, ),.
gsub(/[X().]/,"",my_str)
my_len = trim(my_str)
}
else {
# remove spaces and point
gsub(/[ .]/,"",my_str)
# count number of X
my_len = length(my_str)
}
return my_len
}
function get_numeric_length(pic_num) {
my_str = toupper(pic_num)
if (my_str ~ /\([0-9]+\)/) {
# remove S, spaces, ), .
gsub(/[S ).]/,"",my_str)
# split into a list using delimiter (
split(my_str, my_str_lst,"(")
# take second list element
my_len = trim(my_str_lst[2])
}
else {
# remove spaces and point
gsub(/[ .]/,"",my_str)
# count number of X
my_len = length(my_str)
}
return my_len
}
END OF cbl2sql.awk >>>>>>>>>>>>>>>>>>>>>>>
From:
RMiklos@xxxxxx
To:
"COBOL Programming on the IBM i \(AS/400 and iSeries\)"
<cobol400-l@xxxxxxxxxxxx>
Date:
02.06.2016 15:08
Subject:
Re: [COBOL400-L] Convert COBOL record layout specs to PF or DDL specs?
Sent by:
"COBOL400-L" <cobol400-l-bounces@xxxxxxxxxxxx>
I see that the attached picture - which should demonstarte the usage - was
not delivered into mailing list -so here in the text
mikl1071@MIKL1071 ~/awk/COBOL_copybook
$ cat FOOBAR.cbl
* Example
* COBOL Copy Book
*
01 FOOBAR-AREA.
* Characters
05 FOO PIC X(24).
05 BAR PIC XXX.
05 FOOBAR PIC 99.
* Numbers
FOO BAR BAZ
05 SPAM PIC S9(16) COMP-3.
05 EGGS PIC S9(04)V9(2).
05 SPAM-AND-EGGS PIC S9(07)V999 COMP-3.
mikl1071@MIKL1071 ~/awk/COBOL_copybook
$ awk -f cbl2sql.awk FOOBAR.cbl
create table FOOBAR_AREA (
FOO CHAR(24)
BAR CHAR(3)
FOOBAR DEC(2,0)
SPAM DEC(16,0)
EGGS DEC(6,2)
SPAM-AND-EGGS DEC(10,3)
)
Done.
I have this awk version on my PC:
mikl1071@MIKL1071 ~/awk/COBOL_copybook
$ awk --version
GNU Awk 3.1.7
Copyright (C) 1989, 1991-2009 Free Software Foundation.
On the iSeries I have this older version and it doen't run with it
QSH Command Entry
GNU Awk 3.0.3
Copyright (C) 1989, 1991-1997 Free Software Foundation.
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307,
USA.
/home/ROMANAPS $
===>
F3=Exit F6=Print F9=Retrieve F12=Disconnect
F13=Clear F17=Top F18=Bottom F21=CL command entry
From:
RMiklos@xxxxxx
To:
"COBOL Programming on the IBM i \(AS/400 and iSeries\)"
<cobol400-l@xxxxxxxxxxxx>, Dan <dan27649@xxxxxxxxx>
Date:
02.06.2016 14:55
Subject:
Re: [COBOL400-L] Convert COBOL record layout specs to PF or DDL specs?
Sent by:
"COBOL400-L" <cobol400-l-bounces@xxxxxxxxxxxx>
Hi,
I have only a little home brew utility - better said an awk script (see
https://en.wikipedia.org/wiki/AWK)
which translates a very simple Copy Book Structure (without REDEFINES) a
creates the corresponding SQL Create Statement
We use in tables which are processed by COBOL basically only CHAR and
DECIMAL fields.
For example if we have an copy book like this
* Example
* COBOL Copy Book
*
01 FOOBAR-AREA.
* Characters
05 FOO PIC X(24).
05 BAR PIC XXX.
05 FOOBAR PIC 99.
* Numbers
FOO BAR BAZ
05 SPAM PIC S9(16) COMP-3.
05 EGGS PIC S9(04)V9(2).
05 SPAM-AND-EGGS PIC S9(07)V999 COMP-3.
then the script converts it into
create table FOOBAR_AREA (
FOO CHAR(24)
BAR CHAR(3)
FOOBAR DEC(2,0)
SPAM DEC(16,0)
EGGS DEC(6,2)
SPAM-AND-EGGS DEC(10,3)
)
Unfortunatelly it doesn't run on iSeries, but on the PC, because on the
iSeries I don't have the current awk version, only an old one.
If you are interested I can send you the source. Maybe you can use it as
is , or enhance it for your needs, or only for inspiration, how to
programm the better utility self :-)
The processing looks like this - see the picture
Regards
Roman
From:
Dan <dan27649@xxxxxxxxx>
To:
cobol400-l@xxxxxxxxxxxx
Date:
31.05.2016 20:10
Subject:
[COBOL400-L] Convert COBOL record layout specs to PF or DDL specs?
Sent by:
"COBOL400-L" <cobol400-l-bounces@xxxxxxxxxxxx>
We have a client who supplied us with COBOL record layout specs for a
rather large layout, and I was wondering if anyone has possession of or
has
seen a utility that will convert such specs into SQL DDL or PF specs
before
I go and invent that wheel.
- Dan
midrange.com
