| Part_0 | - download various demo files for later use in later parts. |
| - downloaded from "https://www.uvsoftware.ca/sqldemofiles.zip" | |
| - We suggest setting up 2 user accounts (mysql2 & demo1) | |
| to hold the test subdirs/files & perform the demos. | |
| - You could setup subdirs & files in your home dir if you prefer. |
| Part_1 | - Test/Demo for MySQL |
| - download/install MySQL from www.mysql.com | |
| - create demo database/table menagerie/pet | |
| (see MySQL 5.1 reference manual chapter 3) | |
| - Download "delimited","files" cust1.txt & sales1.txt | |
| - MySQL Tutorial using "delimited","demo","files" from UV Software | |
| - CREATE database 'ar' & tables 'cust1' & 'sales1' | |
| - SHOW tables, DESCRIBE tables, LOAD DATA into tables, SELECT from tables | |
| - JOIN tables matching on customer number | |
| - this is a much more realistic tutorial than the reference manual |
| Part_2 | - Install ODBC & test Micro Focus COBOL OpenESQL to MySQL database |
| - download & install ODBC driver from www.unixodbc.org | |
| - download & install mysql-connector-odbc from www.mysql.com | |
| - setup odbcinst.ini & odbc.ini files | |
| - test connection with 'isql' (interactive utility) | |
| - test OpenESQL ODBC with COBOL programs sqlcob1.cbl & sqlcob2.cbl |
| Part_3 | - Download & Install Oracle database server & client |
| - setup profile (ORACLE_BASE,HOME,OWNER,SID,& PATH) | |
| - use Oracle 'runInstaller' | |
| - run SQL*Plus to startup database server | |
| - Oracle Enterprise Manager Database Control (optional) | |
| - Setup Unix/Linux user 'demo1' for SQL test/demo | |
| - Setup Oracle USER 'demo1' for SQL test/demo | |
| - Setup subdirs & download test files for SQL demo | |
| - SQL demo - CREATE & LOAD table with SQLLDR & control file | |
| - Use SQL*Plus to verify table loaded OK (select * from cust1) |
| Part_4 | - Oracle PRO*COBOL COBOL-API for SQL |
| - demo COBOL programs with embedded 'EXEC SQL' statements | |
| - Download test/demo COBOL programs & compile script | |
| - Instructions to compile & execute demo programs | |
| - Listings of programs, compile script,& PRO*COBOL output | |
| sqlcob1.cbl - create table & INSERT rows from a sequential file | |
| sqlcob2.cbl - FETCH all rows from table & write to a sequential file | |
| sqlcob2.cob - PRO*COBOL expanded code for compile by Micro Focus SX | |
| - in case you are interested (do not normally need this) |
| Part_5 | - Download & Install DB2 |
| - setup user db2demo1, setup subdirs, & copy test files & programs | |
| - compile supplied COBOL programs & test access to DB2 database tables |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
| Part_6 | - Generating batch-files to create & load tables for SQL Server |
| - sample batch-files to create & load SQL tables | |
| - converting fixed-field file cust1.fix to "delimited" cust1.txt | |
| - commands to use generated batch-files to create & load tables | |
| - demo COBOL programs with 'EXEC SQL's to LOAD & UNLOAD a table | |
| sqlms1.cbl - create table & INSERT rows from a sequential file | |
| sqlms2.cbl - FETCH all rows from table & write to a sequential file | |
| testcon4.cbl - short program to test connecting from COBOL |
| Part_7 | - lists 2 C programs to test/demo the MySQL C-API |
| sqlcust1.c INSERTs rows into a table from a "delimited","file" | |
| sqlcust2.c SELECTs rows from a table & writes a "delimited","file" | |
| - Instructions to Download C programs, compile script,& test data files | |
| - Instructions to compile & execute programs (showing expected output) |
| Part_8 | - uycopy-API to SQL databases |
| - uvcopy is an interpretive utility, much easier than writing C programs | |
| - uvcopy is a powerful data manipulation utility from UV Software | |
| - especially valuable for handling data files migrated from mainframes | |
| (process Indexed files & packed data not possible with unix utilities) | |
| - As of November 2008, can now process SQL DataBase tables | |
| while copying from/to "delimited","text","files". | |
| - convert 'Fixed-Fields' to "delimited","fields" to LOAD database tables | |
| - convert "delimited","fields" to 'Fixed-Fields' to UNLOAD database table |
| Part_9 | - preparing test data files for SQL demos |
| - test data files 'cust1.txt' & 'sales1.txt' | |
| - mainframe type files migrated to Unix/Linux/Windows | |
| - translated from EBCDIC to ASCII | |
| - Fixed-Field record layout converted to "delimited","format" | |
| as required for input to SQL databases | |
| - Vancouver Utility 'uvcopy' makes it easy to perform the | |
| translation & reformatting to "delimited". |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
SQLdemo documents downloading & testing 3 databases (MySQL,Oracle,& DB2). UV Software provides various test files & COBOL programs that you can download from the website in case you do not have the Vancouver Utilities installed.
Part 0 documents downloading the test files & COBOL programs that will be used in later parts. The test files are provided in a zip file. Download from:
https://www.uvsoftware.ca/sqldemofiles.zip ===========================================
We suggest downloading into a temp subdir & then copying the desired files to the appropriate subdir for your testing. We call the temp subdir 'tmp2'.
We recommend setting up separate homedirs to test MySQL, Oracle,& DB2. You could setup subdirs & store the files in your home dir if you prefer.
If you setup separate homedirs, it might look like this:
/home/mysql2 <-- homedir for MySQL test files :-----cbls - COBOL programs :-----cpys - copybooks :-----ctl - control files (COBOL directives) :-----dat1 - test data files for COBOL programs, etc :-----sf - script files :-----src - C source programs :-----tmp2 - unzip downloaded files & copy files to subdirs above : :-----sqldemofiles.zip : :-----cbls : :-----cpys : :-----ctl : :-----dat1 : :-----sf : :-----src
/home/demo1 <-- homedir for Oracle test files :-----cbls :-----... same as above :-----tmp2 : :-----... same as above
/home/db2demo1 <-- homedir for DB2 test files :-----cbls :-----... same as above :-----tmp2 : :-----... same as above
Please see the downloaded tmp2 files listed on the next page.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
After downloading & unzipping, all subdirs & files can be listed as follows: Option '-R' is 'recursive' to show files in all subdirs.
ls -lR /home/mysql2/tmp2 ========================
tmp2: total 68 drwxrwxr-x 2 uvadm apps 4096 Jul 29 07:33 cbls drwxrwxr-x 2 uvadm apps 4096 Jul 29 07:33 cpys drwxrwxr-x 2 uvadm apps 4096 Jul 29 07:33 ctl drwxrwxr-x 2 uvadm apps 4096 Jul 29 07:33 dat1 drwxrwxr-x 2 uvadm apps 4096 Jul 29 07:33 sf -rw-r--r-- 1 uvadm apps 41205 Jul 29 07:33 sqldemofiles.zip drwxrwxr-x 2 uvadm apps 4096 Jul 29 07:33 src
tmp2/cbls: total 100 -rw-rw-r-- 1 uvadm apps 21142 Jul 29 07:33 nameadrs1.cbl -rw-rw-r-- 1 uvadm apps 4657 Jul 29 07:33 sqldb21.cbl -rw-rw-r-- 1 uvadm apps 4460 Jul 29 07:33 sqldb22.cbl -rw-rw-r-- 1 uvadm apps 4168 Jul 29 07:33 sqlmyo1.cbl -rw-rw-r-- 1 uvadm apps 4270 Jul 29 07:33 sqlmyo2.cbl -rw-rw-r-- 1 uvadm apps 4368 Jul 29 07:33 sqlora1.cbl -rw-rw-r-- 1 uvadm apps 4275 Jul 29 07:33 sqlora1w.cbl -rw-rw-r-- 1 uvadm apps 4467 Jul 29 07:33 sqlora2.cbl -rw-rw-r-- 1 uvadm apps 4491 Jul 29 07:33 sqlora2w.cbl -rw-r--r-- 1 uvadm apps 1824 Jul 29 07:33 testcon1.cbl -rw-r--r-- 1 uvadm apps 2134 Jul 29 07:33 testcon2.cbl -rw-r--r-- 1 uvadm apps 1804 Jul 29 07:33 testcon3.cbl
tmp2/cpys: total 8 -rw-rw-r-- 1 uvadm apps 692 Jul 29 07:33 cust1.cpy -rw-rw-r-- 1 uvadm apps 1061 Jul 29 07:33 sqlca.cpy
tmp2/ctl: total 12 -rw-rw-r-- 1 uvadm apps 1987 Jul 29 07:33 cobdirectives -rw-rw-r-- 1 uvadm apps 356 Jul 29 07:33 db2createloadcust1 -rw-rw-r-- 1 uvadm apps 2589 Jul 29 07:33 extfh.cfg
tmp2/dat1: total 32 -rw-rw-r-- 1 uvadm apps 432 Jul 29 07:33 cust1.dat -rw-rw-r-- 1 uvadm apps 640 Jul 29 07:33 cust1.fix -rw-rw-r-- 1 uvadm apps 479 Jul 29 07:33 cust1.txt -rw-rw-r-- 1 uvadm apps 264 Jul 29 07:33 pet.txt -rw-rw-r-- 1 uvadm apps 640 Jul 29 07:33 sales1.fix -rw-rw-r-- 1 uvadm apps 600 Jul 29 07:33 sales1_origA -rw-rw-r-- 1 uvadm apps 600 Jul 29 07:33 sales1_origE -rw-rw-r-- 1 uvadm apps 680 Jul 29 07:33 sales1.txt
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
tmp2/sf: total 36 -rwxrwxr-x 1 uvadm apps 836 Jul 29 07:33 ccmysql -rwxrwxr-x 1 uvadm apps 1014 Jul 29 07:33 createcust1 -rwxrwxr-x 1 uvadm apps 2045 Jul 29 07:33 createcust1b -rwxrwxr-x 1 uvadm apps 981 Jul 29 07:33 createsales1 -rwxrwxr-x 1 uvadm apps 2167 Jul 29 07:33 createsales1b -rwxrwxr-x 1 uvadm apps 722 Jul 29 07:33 cust1.ctl -rwxrwxr-x 1 uvadm apps 718 Jul 29 07:33 cust1.sql -rwxrwxr-x 1 uvadm apps 2811 Jul 29 07:33 mfcbl1 -rwxrwxr-x 1 uvadm apps 2524 Jul 29 07:33 mfprocob1
tmp2/src: total 28 -rw-rw-r-- 1 uvadm apps 1136 Jul 29 07:33 mysql0.c -rw-rw-r-- 1 uvadm apps 5184 Jul 29 07:33 mysql2.c -rw-rw-r-- 1 uvadm apps 6299 Jul 29 07:33 sqlcust1.c -rw-rw-r-- 1 uvadm apps 6929 Jul 29 07:33 sqlcust2.c
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Here is the script used at UV Software to create the zip file archive. This shows the location of the various test files in the uvadm directory. If you have Vancouver Utilities, you could copy directly rom uvadm/... to your MySQL, Oracle,or DB2 test directories.
# zipsqldemofiles - create sqldemofiles.zip for upload to web site # - by Owen Townsend, UV Software, May 20/2009 # - see www.uvsoftware.ca/sqldemo.htm # rm -rf tmp2/* mkdir tmp2/cbls tmp2/cpys tmp2/ctl tmp2/dat1 tmp2/sf tmp2/src cp mf/cbls/sql* tmp2/cbls cp mf/cbls/testcon* tmp2/cbls cp mf/cbls/nameadrs1.cbl tmp2/cbls cp mf/cpys/cust1* tmp2/cpys cp mf/cpys/sqlca* tmp2/cpys cp ctl/cobdirectives tmp2/ctl cp ctl/extfh.cfg tmp2/ctl cp ctl/db2createloadcust1 tmp2/ctl cp dat1/pet* tmp2/dat1 cp dat1/cust1.* tmp2/dat1 cp dat1/sales1.* tmp2/dat1 cp dat1/sales1_* tmp2/dat1 cp sf/demo/ccmysql tmp2/sf cp sf/IBM/mfprocob1 tmp2/sf cp sf/IBM/mfcbl1 tmp2/sf cp sf/demo/createcust* tmp2/sf cp sf/demo/createsales* tmp2/sf cp sf/demo/cust1* tmp2/sf cp src/mysql* tmp2/src cp src/sql* tmp2/src cd tmp2 zip -r sqldemofiles.zip . exit 0
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
| 1A1. | Introduction to these MySQL test/demos & tutorials |
| - Preparations to Download/Install MySQL |
| 1A2. | Setup 'mysql2' account & subdirs for MySQL test/demo files |
| 1A3. | Download MySQL files (5.1 in October 2008) |
| - rpm files & pdf documentation |
| 1A4. | Post Install Procedures (refman 2.10.2) |
| - setup initial 'grant' table root userid/password |
| 1A5. | setup user Accounts & Passwords (refman 5.5.2) |
| - 'mysql2' for demo to avoid confusion with 'mysql' account |
| 1B1. | MySQL Tutorial (refman chapter 3) |
| 1B2. | Insert data into 'pet' table (slow way) |
| 1B3. | Better Ways to Load Tables |
| 1C1. | UV Software demo test/demo files for SQL Tutorial |
| - "delimited","files" to load SQL tables |
| 1C2. | download "delimited",files" from |
| 'https://www.uvsoftware.ca/sqldemofiles.zip'. | |
| - cust1.txt (Name & Address) & sales1.txt (sales details) |
| 1C3. | UV Software SQL Tutorial Operating Instructions |
| - using cust1 & sales1 tables | |
| - JOIN tables on customer number, etc |
| 1D1. | SQL 'BATCH' files for long commands such as CREATE table. |
| - so you can easily re-create when necessary. | |
| - can call batch file from shell script to save running the mysql client | |
| - sample batch files (createcust1b & createsales1b) | |
| - sample shell scripts to call batch files (createcust1 & createsales1) |
| 1E1. | File Naming Conventions, Why we append '1' on cust1, sales1, etc. |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
This 'SQLdemo' gives you the step by step procedures to download the MySQL software from www.mysql.com, and setup a testing directory with demo files that you can download from www.uvsoftware.ca.
The test files from UV Software are much more realistic than the 'pet' table used in the MySQL reference manual tutorial. 'cust1.txt' & 'sales1.txt' are "delimited","text","files" for loading database tables 'ar.cust1' & 'ar.sales1'.
The UV Software tutorial then guides you thru the various SQL commands (CREATE tables, LOAD tables, SELECT from, etc). Since our demo tables have a common customer number field, you can perform a 'JOIN' to select sales1 items matching the customers in the cust1 table.
You do NOT need the Vancouver Utilities software package to perform the tutorials in Part 1. In Part_9, I will demonstrate how the 'uvcopy' utility can help any SQL site create the "delimited","files" required to load SQL database tables.
I will describe the procedures I used to download & install MySQL 5.0 on my Red Hat Enterprise Linux 5.1 (running on 64 bit AMD Opteron HP XW9400). You must of course select the download files appropriate to your O/S & hardware.
I setup user 'mysql2' to hold the various subdirs & files I would require for the project. I used userid 'mysql2' (homedir /home/mysql2) to avoid any confusion with user 'mysql' automatically setup by MySQL install.
Please see https://www.uvsoftware.ca/admjobs.htm#1D2 for the root commands 'adduser' & 'passwd'. I installed the profile recommended for the Vancouver Utilities. You do NOT need the Vancouver Utilities for the SQL demos in Part 1. My only use in Part 1 was for the laser printing scripts, such as 'uvlp12'. If interested you can see it listed at https://www.uvsoftware.ca/uvlist.htm#E1
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login root
==========
#2. adduser -m -g apps -d /home/mysql2 mysql2
=========================================
#3. passwd mysql2 - set password 'mysql200'
=============
#4. cp -p /home/uvadm/env/stub_profile /home/mysql2/.bash_profile
=============================================================
- if you have Vancouver Utilities, you could copy profile as shown above
| #4a. | 'https://www.uvsoftware.ca/admjobs.htm#Part_1' |
| ============================================= | |
| Else - see suggested profiles at URL above | |
| OR - create your own profile as desired |
#1. Login user 'mysql2' --> /home/mysql2
#2. mkdir bin dat1 doc rpm sf src tmp
=================================
- make subdirs for downloads, test files, & C demo program
#3. dtree /home/mysql2 <-- script 'dtree' displays subdirs
==================
| Note |
|
/home/mysql2 :-----dat1 : :-----pet.txt <-- '|' delimited demo file to load 'pet' table : : :-----doc <-- documentation (downloaded from www.mysql.com) : :-----refman-5.1-en.pdf : : :-----download <-- rpm files downloaded from www.mysql.com : :-----MySQL-client-community-5.0.67-0.rhel5.x86_64.rpm : :-----MySQL-devel-community-5.0.67-0.rhel5.x86_64.rpm : :-----MySQL-server-community-5.0.67-0.rhel5.x86_64.rpm
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Use the web browser to download the following files. Store them in /home/mysql2/download & /home/mysql2/doc (see subdirs illustrated above).
Download the 'rpm' files from https://dev.mysql.com/download/5.0.html.
Download documentation from https://downloads.mysql.com/docs/refman-5.1-en.pdf
As of November 2008, the software available was 5.0, but we downloaded the 5.1 reference manual in case of later upgrade (& not much different).
The reference manual is over 2000 pages. I found most of what I needed in the first 200 (install & tutorial) and 1900-2000 (C-API). I printed on my Lexmark T542 (35 ppm Duplex).
#1. Login as root
=============
#2. cd /home/mysql2/download
========================
#3a. rpm -i MySQL-server-community-5.0.67-0.rhel5.x86_64.rpm
=======================================================
#3b. rpm -i MySQL-client-community-5.0.67-0.rhel5.x86_64.rpm
=======================================================
#3c. rpm -i MySQL-devel-community-5.0.67-0.rhel5.x86_64.rpm
======================================================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login as root
=============
#2. cd /var/lib/mysql
=================
- where MySQL install on Red Hat sets up the 'mysql' directory
#3. /usr/bin/mysql_install_db --user=mysql
======================================
- setup initial 'grant' (mysql user/password) tables
#4. /usr/bin/mysqld_safe --user=mysql &
===================================
- start the MySQL server (if not started by install procedure)
#5. ls -lR /var/lib/mysql <-- list mysql subdirs & files after install
===================== - 'R' recursive option lists files in subdirs
-rw-rw---- 1 mysql mysql 10485760 Oct 28 19:48 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Oct 29 06:29 ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Oct 22 17:16 ib_logfile1 drwx------ 2 mysql mysql 4096 Oct 23 13:40 menagerie drwx------ 2 mysql mysql 4096 Oct 22 17:09 mysql srwxrwxrwx 1 mysql mysql 0 Oct 29 06:29 mysql.sock drwx------ 2 mysql mysql 4096 Oct 22 17:09 test
./menagerie: <-- database created by Tutorial (refman 3.3.1) -rw-rw---- 1 mysql mysql 65 Oct 23 12:09 db.opt -rw-rw---- 1 mysql mysql 8720 Oct 23 12:11 pet.frm -rw-rw---- 1 mysql mysql 220 Oct 23 13:32 pet.MYD <-- table setup by Tutorial -rw-rw---- 1 mysql mysql 1024 Oct 23 20:39 pet.MYI
./mysql: -rw------- 1 mysql mysql 9494 Oct 22 17:09 db.frm -rw------- 1 mysql mysql 876 Oct 22 17:09 db.MYD -rw------- 1 mysql mysql 4096 Oct 22 17:09 db.MYI -rw------- 1 mysql mysql 9416 Oct 22 17:09 host.frm -rw------- 1 mysql mysql 0 Oct 22 17:09 host.MYD -rw------- 1 mysql mysql 1024 Oct 22 17:09 host.MYI -rw------- 1 mysql mysql 8947 Oct 22 17:09 tables_priv.frm -rw------- 1 mysql mysql 0 Oct 22 17:09 tables_priv.MYD -rw------- 1 mysql mysql 1024 Oct 22 17:09 tables_priv.MYI -rw------- 1 mysql mysql 10330 Oct 22 17:09 user.frm -rw------- 1 mysql mysql 304 Oct 24 09:01 user.MYD -rw------- 1 mysql mysql 2048 Oct 24 19:38 user.MYI - several files omitted from ./mysql/... to save space
./test: -rw-rw---- <-- could download TEST databases
| Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
We will setup account 'mysql2' (password 'mysql200') to perform the tutorial, and load database 'menagerie' with table 'pet'.
#1. Login as root
=============
#2. cd /var/lib/mysql
=================
#3. mysql mysql <-- run the 'mysql' client program
===========
mysql> <-- Note that the prompt changes to 'mysql>'
#4. grant all privileges on *.* to 'mysql2'@'localhost'
===================================================
identified by 'mysql200' with grant option;
===========================================
| Note |
|
mysql> ->
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login as mysql2 --> /home/mysql2
================================
#2. mysql -u mysql2 -pmysql200 <-- start the mysql 'client' program
========================== - user 'mysql2' & password 'mysql200'
| Note |
|
Try the various SQL commands suggested by the tutorial in chapter 3, then perform the following commands to load the menagerie/pet table
Note that I will show the results that we will get after the menagerie/pet database/table have been created.
#3. show databases;
===============
Database
information_schema
menagerie
mysql
test
#4. create database menagerie;
==========================
#5. use menagerie;
==============
#6. create table pet (name varchar(20), owner varchar(20),
======================================================
species varchar(20), sex char(1), birth date, death date);
==========================================================
#7. show tables;
============
Tables_in_menagerie
pet
#8. describe pet
============
Field Type Null Key Default Extra
name varchar(20) YES NULL
owner varchar(20) YES NULL
species varchar(20) YES NULL
sex char(1) YES NULL
birth date YES NULL
death date YES NULL
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#9a. insert into pet values ('Fluffy','Harold','cat','f','1999-03-30',NULL);
=======================================================================
- - - etc, see pet table data on page 3.3.3 of refman tutorial chapter 3
#9h. insert into pet values ('Slim','Benny','snake','m','1996-04-29',NULL);
=======================================================================
#10. select * from pet;
==================
name owner species sex birth death
Fluffy Harold cat f 1993-02-04 0000-00-00
Claws Gwen cat m 1994-03-17 0000-00-00
Buffy Harold dog f 1989-05-13 0000-00-00
Fang Benny dog m 1990-08-27 0000-00-00
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1989-09-11 0000-00-00
Whistler Gwen bird 1997-12-09 0000-00-00
Slim Benny snake m 1996-04-29 0000-00-00
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
As you can see above, the 'INSERT' command to load tables would be very laborious. It would be faster to edit the table offline & then 'load' the table in mysql. Of course the table data may be generated automatically by various application programs (COBOL, internet, etc).
#1. Login as mysql2 --> /home/mysql2
================================
#2. vi dat1/pet.txt <-- edit the table offline
=============== - I prefer the '|' pipe delimiter (vs invisible tab)
Fluffy|Harold|cat|f|1993-02-04||
Claws|Gwen|cat|m|1994-03-17||
Buffy|Harold|dog|f|1989-05-13||
Fang|Benny|dog|m|1990-08-27||
Bowser|Diane|dog|m|1979-08-31|1995-07-29|
Chirpy|Gwen|bird|f|1989-09-11||
Whistler|Gwen|bird||1997-12-09||
Slim|Benny|snake|m|1996-04-29||
#3. mysql -pmysql200 <-- start the mysql 'client' program
================ - do not need '-u mysql2' if unix login is 'mysql2'
#4. use menagerie;
==============
#5. load data infile 'dat1/pet.txt' into table pet
==============================================
fields terminated by '|';
=========================
Alternatively we could use the batch command 'mysqlimport' to load the table from our unix/linux prompt without running under the mysql client.
#1. Login as mysql2 --> /home/mysql2
================================
#2. mysqlimport -pmysql200 --local menagerie dat1/pet.txt
=====================================================
| Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
UV Software provides some fixed & "delimited","files" that you can download for loading into MySQL tables for use in the tutorials begining on page '1C3'.
130140 EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC 139923 JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC 150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC 201120 ALLTYPE RENTAL LTD BOX 1819 DRAYTON AL 308685 FOOTHILLS ELECTRIC 3932 - 3A ST. NW CALGARY AL 315512 PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB 400002 ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC 406082 PRECAM RENTALS LTD 10116-94TH AVE. FORT ST. JOHN BC
130140,"EVERGREEN MOTORS LTD.","1815 BOWEN ROAD","NANAIMO","BC"
139923,"JOHNSTONE BOILER","1250 EAST PENDER ST.","VANCOUVER","BC"
150825,"RIGGERS INDUSTRIAL","960 - 6TH AVENUE","HOPE","BC"
201120,"ALLTYPE RENTAL LTD.","BOX 1819","DRAYTON VALLEY","AL"
308685,"FOOTHILLS ELECTRIC","3932 - 3A ST. N.W.","CALGARY","AL"
315512,"PARTS PLUS","BOX 510 MAIN ST.","THREE HILLS","AB"
400002,"ACKLANDS LTD","945 -2ND AVE.","PRINCE GEORGE","BC"
406082,"PRECAM RENTALS LTD.","10116-94TH AVE.","FORT ST. JOHN","BC"
130140,21,2004-08-02,"INV11201","HAM001",000010,00012.00,0000120.00
150825,44,2006-08-04,"INV11202","HAM001",000011,00010.00,0000110.00
150825,44,2006-08-05,"INV11203","HAX129",000012,00020.00,0000240.00
223240,65,2008-08-16,"CRED9505","HAM001",-00013,00012.00,-000156.00
223240,65,2008-08-17,"INV44103","BBQ001",000014,00100.00,0001400.00
308685,21,2009-08-14,"CRED8835","TAB013",-00015,00200.00,-003200.00
308685,21,2009-08-12,"INV66058","SAW051",000016,00150.00,0002250.00
406082,35,2002-08-15,"INV33001","BBQ001",000017,00100.00,0001700.00
406082,35,2002-08-16,"INV33001","TAB013",000018,00280.00,0005040.00
406082,65,2002-08-16,"INV44199","HAM001",000019,00012.00,0000228.00
These files were originally designed on a mainframe as fixed-length records.
The Vancouver Utilities include 'uvcopy jobs' that make it easy to convert fixed-length records to "delimited","fields", for loading into SQL tables. See the details in 'Part_9'.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
The test/demo (cust1.txt & sales1.txt, listed above) are provided on the web site in zip file 'sqldemofiles.zip'
Please see Part_0 for the instructions to download, unzip,& copy to /home/mysql2/dat1/...
/home/mysql2 :-----bin : :-----sqlcust1 <-- executables for demo programs : :-----sqlcust2 :-----dat1 : :-----cust1.txt <-- customer Name & Address "delimited" file : :-----sales1.txt <-- sales detail "delimited" file :-----sf : :-----ccmysql <-- script to compile C programs with MySQL API :-----src : :-----sqlcust1.c <-- C program to INSERT rows from "delimited","file" : :-----sqlcust2.c <-- C program to SELECT rows & write to "delimited"
We only need the dat1/... files for the Tutorial here in Part 1, but I am also showing other subdirs that we will use in Part_7 to demo the MySQL C-API.
When we 'LOAD DATA INTO' tables, MySQL will store them in /var/lib/mysql/... You do not have to know where they are, but in case you are wondering:
/var/lib/mysql ============== ./ar/ <-- database 'AR' -rw-rw---- 1 mysql mysql 65 Oct 23 12:09 db.opt <-- options -rw-rw---- 1 mysql mysql 8720 Oct 23 12:11 cust1.frm <-- cust1 formats -rw-rw---- 1 mysql mysql 220 Oct 23 13:32 cust1.MYD <-- cust1 data -rw-rw---- 1 mysql mysql 1024 Oct 23 13:39 cust1.MYI <-- cust1 index -rw-rw---- 1 mysql mysql 8720 Oct 23 14:11 sales1.frm <-- sales1 formats -rw-rw---- 1 mysql mysql 220 Oct 23 15:32 sales1.MYD <-- sales1 data -rw-rw---- 1 mysql mysql 1024 Oct 23 15:39 sales1.MYI <-- sales1 index
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
| Note |
|
#1. Login as mysql2 --> /home/mysql2
================================
#2. mysql -pmysql200 <-- start the mysql 'client' program
================ - do not need '-u mysql2' if unix login is 'mysql2'
mysql> _______; <-- prompt changes to 'mysql>'
& you must terminate your SQL command with ';'
#3. create database ar; <-- create database for cust1 & sales1 tables
===================
#4. use ar; <-- must select (use) the desired database
=======
#5a. create table ar.cust1 (custno numeric(6) primary key, name char(22)
===================================================================
,adrs char(22), city char(16), prov char(2));
=============================================
#5b. create table ar.sales1 (custno numeric(6), slm numeric(2), invdate date
=======================================================================
,invoice char(8), product char(6), qty numeric(6)
=================================================
,price numeric(7,2), amount numeric(9,2));
==========================================
| Note |
|
#6. show tables;
============
+--------------+
| Tables_in_ar |
+--------------+
| cust1 |
| sales1 |
+--------------+
2 rows in set (0.00 sec)
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#7a. describe cust1;
===============
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| custno | decimal(6,0) | NO | PRI | NULL | |
| name | char(22) | YES | | NULL | |
| adrs | char(22) | YES | | NULL | |
| city | char(16) | YES | | NULL | |
| prov | char(2) | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
#7b. describe sales1;
================
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| custno | decimal(6,0) | YES | | NULL | |
| slm | decimal(2,0) | YES | | NULL | |
| invdate | date | YES | | NULL | |
| invoice | char(8) | YES | | NULL | |
| product | char(6) | YES | | NULL | |
| qty | decimal(6,0) | YES | | NULL | |
| price | decimal(7,2) | YES | | NULL | |
| amount | decimal(9,2) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#8a. load data local infile 'dat1/cust1.txt' into table ar.cust1
===========================================================
fields terminated by ',' optionally enclosed by '"';
====================================================
#8b. load data local infile 'dat1/sales1.txt' into table ar.sales1
=============================================================
fields terminated by ',' optionally enclosed by '"';
====================================================
#9a. select * from ar.cust1;
=======================
+--------+--------------------+------------------+---------------+------+ | custno | name | adrs | city | prov | +--------+--------------------+------------------+---------------+------+ | 130140 | EVERGREEN MOTORS | 1815 BOWEN ROAD | NANAIMO | BC | | 139923 | JOHNSTONE BOILER | 1250 EAST PENDER | VANCOUVER | BC | | 150825 | RIGGERS INDUSTRIAL | 960 - 6TH AVENUE | HOPE | BC | | 201120 | ALLTYPE RENTAL LTD | BOX 1819 | DRAYTON | AL | | 308685 | FOOTHILLS ELECTRIC | 3932 3RD ST. NW | CALGARY | AL | | 315512 | PARTS PLUS | BOX 510 MAIN ST. | THREE HILLS | AB | | 400002 | ACKLANDS LTD | 945 -2ND AVE. | PRINCE GEORGE | BC | | 406082 | PRECAM RENTALS LTD | 10116-94TH AVE. | FORT ST. JOHN | BC | +--------+--------------------+------------------+---------------+------+ 8 rows in set (0.00 sec)
#9b. select * from ar.sales1;
========================
+--------+------+------------+----------+---------+------+--------+----------+ | custno | slm | invdate | invoice | product | qty | price | amount | +--------+------+------------+----------+---------+------+--------+----------+ | 130140 | 21 | 2004-08-02 | INV11201 | HAM001 | 10 | 12.00 | 120.00 | | 150825 | 44 | 2006-08-04 | INV11202 | HAM001 | 11 | 10.00 | 110.00 | | 150825 | 44 | 2006-08-05 | INV11203 | HAX129 | 12 | 20.00 | 240.00 | | 223240 | 65 | 2008-08-16 | CRED9505 | HAM001 | -13 | 12.00 | -156.00 | | 223240 | 65 | 2008-08-17 | INV44103 | BBQ001 | 14 | 100.00 | 1400.00 | | 308685 | 21 | 2009-08-14 | CRED8835 | TAB013 | -15 | 200.00 | -3200.00 | | 308685 | 21 | 2009-08-12 | INV66058 | SAW051 | 16 | 150.00 | 2250.00 | | 406082 | 35 | 2002-08-15 | INV33001 | BBQ001 | 17 | 100.00 | 1700.00 | | 406082 | 35 | 2002-08-16 | INV33001 | TAB013 | 18 | 280.00 | 5040.00 | | 406082 | 65 | 2002-08-16 | INV44199 | HAM001 | 19 | 12.00 | 228.00 | +--------+------+------------+----------+---------+------+--------+----------+ 10 rows in set (0.00 sec)
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Since our demo tables have a common field (custno) we can 'JOIN' the tables, selecting sales items from the sales1 table for each customer in the cust1 table. There is no 'JOIN' command, 'JOIN' is simply a term used to describe 'SELECT's from 2 tables with matches specified via a 'WHERE' clause.
#10. SELECT cust1.name, sales1.invoice, sales1.product, sales1.amount
================================================================
FROM cust1, sales1 WHERE cust1.custno = sales1.custno
=====================================================
ORDER BY cust1.name, sales1.invoice;
====================================
+-----------------------+----------+---------+----------+ | name | invoice | product | amount | +-----------------------+----------+---------+----------+ | EVERGREEN MOTORS LTD. | INV11201 | HAM001 | 120.00 | | FOOTHILLS ELECTRIC | CRED8835 | TAB013 | -3200.00 | | FOOTHILLS ELECTRIC | INV66058 | SAW051 | 2250.00 | | PRECAM RENTALS LTD. | INV33001 | TAB013 | 5040.00 | | PRECAM RENTALS LTD. | INV33001 | BBQ001 | 1700.00 | | PRECAM RENTALS LTD. | INV44199 | HAM001 | 228.00 | | RIGGERS INDUSTRIAL | INV11202 | HAM001 | 110.00 | | RIGGERS INDUSTRIAL | INV11203 | HAX129 | 240.00 | +-----------------------+----------+---------+----------+ 8 rows in set (0.02 sec)
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Since 'create table' commands can be very long, is a good idea to set up a 'batch file' to create & recreate when necessary.
# createcust1b - 'Batch file' to create SQL database table 'cust1'
# - by Owen Townsend, UV Software, Nov 07/2008
# - see www.uvsoftware.ca/sqldemo.htm#1D1
#
# We will illustrate 3 ways to use this 'SQL BATCH file' assuming:
# - logged in as unix/linux userid 'mysql2' & mysql password 'mysql200'
# - in homedir /home/mysql2 with subdirs:
# /home/mysql2/sf/createcust1b <-- this batch file
# /home/mysql2/dat1/cust1.txt <-- "delimited","data","file"
# /home/mysql2/sf/createcust1 <-- shell script to run batch file
#
##1. If you are running the MySQL client (program 'mysql')
#
# mysql> source sf/createcust1b <-- use 'source' to run batch file
# ======================
#
##2. If you wish to run from the unix/linux shell prompt:
#
# shell> mysql -pmysql200 <sf/createcust1b <-- run mysql with redirected input
# ================================= from the batch file
#
##3. Run a shell script calling the batch file
#
# shell> createcust1 sf/createcust1b <-- run shell script calling batch file
# =========================== - significant code same as #2
#
##4. You could use 'mysqlimport' to load the table (vs using this batch file)
# But the table would have to have already been CREATEd
#
# shell> mysqlimport -pmysql200 --local ar dat1/cust1.txt
# ================================================
#
# MySQL BATCH file commands to create ar.cust1 table:
#
drop table if exists ar.cust1;
#=============================
create table ar.cust1 (custno numeric(6) primary key, name char(22),
adrs char(22), city char(16), prov char(2));
#=====================================================================
load data local infile 'dat1/cust1.txt' into table ar.cust1
fields terminated by ',' optionally enclosed by '"';
#===================================================================
select * from ar.cust1; # display table contents as confirmation
#======================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
The 'batch file' (listed above) documented 3 ways to use it. Method #3 is to write a shell scripts to call the batch file, which saves you from having to run the MySQL 'client' program interactively (could run by cron).
# createcust1 - create database table 'cust1' via # this script 'createcust1' & batch file 'createcust1b' # - by Owen Townsend, UV Software, Nov 07/2008 # - see www.uvsoftware.ca/sqldemo.htm#1D1 # # Assumptions: # - logged in as unix/linux userid 'mysql2' & mysql password 'mysql200' # - in homedir /home/mysql2 with subdirs: # /home/mysql2/sf/createcust1 <-- shell script to run batch file # /home/mysql2/sf/createcust1b <-- this batch file # /home/mysql2/dat1/cust1.txt <-- "delimited","data","file" # # Run this shell script from the shell prompt as follows: # # shell> createcust1 sf/createcust1b <-- run shell script calling batch file # =========================== # # shell> createcust1 <-- same as above (batch filename defaults as shown) # =========== # bf="$1" # capture batch filename from arg1 (if not defaulted) if [[ ! -f "$bf" ]]; then bf=sf/createcust1b; fi # mysql -pmysql200 < $bf #===================== exit 0
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
# createsales1b - 'Batch file' to create SQL database table 'sales1'
# - by Owen Townsend, UV Software, Nov 07/2008
# - see www.uvsoftware.ca/sqldemo.htm#1D1
#
# We will illustrate 3 ways to use this 'SQL BATCH file' assuming:
# - logged in as unix/linux userid 'mysql2' & mysql password 'mysql200'
# - in homedir /home/mysql2 with subdirs:
# /home/mysql2/sf/createsales1b <-- this batch file
# /home/mysql2/dat1/sales1.txt <-- "delimited","data","file"
# /home/mysql2/sf/createcuast1 <-- shell script to run batch file
#
##1. If you are running the MySQL client (program 'mysql')
#
# mysql> source sf/createsales1b <-- use 'source' to run batch file
# =======================
#
##2. If you wish to run from the unix/linux shell prompt:
#
# shell> mysql -pmysql200 <sf/createsales1b <-- run mysql with redirected input
# ================================== from the batch file
#
##3. Run a shell script calling the batch file
#
# shell> createsales1 sf/createsales1b <-- run shell script calling batch file
# ============================= - significant code same as #2
#
##4. You could use 'mysqlimport' to load the table (vs using this batch file)
# But the table would have to have already been CREATEd
#
# shell> mysqlimport -pmysql200 --local ar dat1/sales1.txt
# =================================================
#
# MySQL BATCH file commands to create ar.sales1 table:
#
drop table if exists ar.sales1;
#==============================
create table ar.sales1 (custno numeric(6), slm numeric(2), invdate date,
invoice char(8), product char(6), qty numeric(6),
price numeric(7,2), amount numeric(9,2));
#==========================================================================
load data local infile 'dat1/sales1.txt' into table ar.sales1
fields terminated by ',' optionally enclosed by '"';
#=============================================================
select * from ar.sales1; # display table as confirmation
#=======================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
# createsales1 - create database table 'sales1' via batch file 'createsales1b' # - by Owen Townsend, UV Software, Nov 07/2008 # - see www.uvsoftware.ca/sqldemo.htm#1D1 # # Assumptions: # - logged in as unix/linux userid 'mysql2' & mysql password 'mysql200' # - in homedir /home/mysql2 with subdirs: # /home/mysql2/sf/createsales1 <-- shell script to run batch file # /home/mysql2/sf/createsales1b <-- this batch file # /home/mysql2/dat1/sales1.txt <-- "delimited","data","file" # # Run this shell script from the shell prompt as follows: # # shell> createsales1 sf/createsales1b <-- run shell script calling batch file # ============================= # # shell> createsales1 <-- same as above (batch filename defaults as shown) # ============ # bf="$1" # capture batch filename from arg1 (if not defaulted) if [[ ! -f "$bf" ]]; then bf=sf/createsales1b; fi # mysql -pmysql200 < $bf #===================== exit 0
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
We have appended a '1' on the end of our filenames ('cust1', 'sales1', etc). This provides several long lasting advantages thru out the life of our applications:
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
| 2A1. | COBOL OpenESQL to MySQL via ODBC - Overview |
| 2B1. | Directories for ODBC Download/Install |
| 2B2. | study the ODBC documentation |
| 2C1. | Install unixODBC 'Driver manager' |
| 2C2. | unixODBC files installed |
| 2D1. | install ODBC connector from MySQL |
| 2E1. | setup Environmental Variables in Profile |
| - edit odbcinst.ini & odbc.ini |
| 2E2. | ODBC 'user' config file |
| ODBC 'system' config file |
| 2E3. | MySQL 'system' configuration file (/etc/my.cnf) |
| MySQL 'user' configuration file .my.cnf in user homedir |
| 2F1. | 'isql' to test ODBC setup |
| 2G1. | mysql2 subdirs/files required to demo COBOL & MySQL/ODBC |
| - download sqldemofiles.zip, unzip,& copy to subdirs | |
| - or copy from uvadm/... if you have Vancouver utilities installed |
| 2H1. | COBOL DIRECTIVES for databases MySQL/ODBC, Oracle,& DB2 |
| - edit ctl/cobdirectives, activate 'SQL (dbman=ODBC) |
| 2I1. | test COBOL OpenESQL ODBC to MySQL DB |
| - compile & execute COBOL programs to load & unload tables |
| 2J1. | test/demo COBOL program listings |
| 2J1. | sqlmyo1.cbl - load table from text file |
| 2J2. | sqlmyo2.cbl - unload table to text file |
| 2J3. | testcon1.cbl - test CONNECT to database |
| 2X1. | Initial problems using COBOL OPenESQL to access MySQL via ODBC |
| And how they were solved (not without a lot of time & effort). |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Part 2 will demonstrate using OpenESQL & 'ODBC' to access MySQL DataBases to process 'EXEC SQL' statements embedded in Micro Focus COBOL programs.
Part_4 will demonstrate how to access Oracle Databases using 'PROCOBOL' and 'ORACLEOCI', which are proprietary. In theory OpenESQL & ODBC should be able to access any RDBMS conforming to the Open standards.
I had some initial problems getting COBOL OpenESQL to work with ODBC (see details on page '2G2').
I did have interactive acccess to ODBC working. I could use 'isql' to execute manual SQL queries on MySQL tables, so I think it was only the COBOL access that was not working.
Perhaps the COBOL problem is a 32bit/64bit conflict, since Micro Focus say their current OpenESQL version 5.1 is 32 bit, but I am running on 64 bit AMD Opteron and the only drivers I could find on MySQL's website (for my machine) are 64 bit.
I have documented my attempts & solutions on page '2G2'.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
I used the following directories to receive the ODBC software & documentation that I downloaded from mysql.com, unixODBC.org,& microfocus.com. See page '1A2' for the original setup of user 'mysql2' & the profile.
/home/mysql2 :-----doc <-- pdf files downloaded & printed out duplex : :-----connector-odbc-en.pdf <-- ODBC install guide : :-----refman-5.0-en.pdf <-- MySQL install & reference : : - used in Part_1 to install/test MySQL : : :-----download <-- download software prior to install : :-----unixODBC-2.2.14.tar.gz : :-----mysql-connector-odbc-3.51.27-linux-x86-32bit.tar.gz : : :-----unixODBC_32 <-- setup subdir to unzip/untar download file : :-----unixODBC-2.2.14.tar.gz : :-----INSTALL <-- print & study : :-----README <-- print & study : :-----...etc... <-- many subdirs & files from unzip/untar : : :-----myodbc351 <-- setup subdir to unzip/untar download file : :-----INSTALL <-- print & study : :-----README <-- print & study : :-----lib <-- Drivers (copy to /usr/local/lib32) : :-----...etc... <-- other subdirs & files from unzip/untar : :
/usr/local :-----etc <-- ODBC config files (see listing page '2E2') : :-----odbc.ini <-- user DSNs (Data Source Names) : :-----odbcinst.ini <-- system drivers : :
Go to the sites listed below & download the required sotware & documentation into the directories shown above.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
https://dev.mysql.com/downloads/connector/odbc/3.51.html ========================================================== - download following 'rpm's & store as shown above
https://dev.mysql.com/doc/refman/5.1/en/connector-odbc.html ========================================================== - HTML doc above, But I recommend you download & print the .pdf as follows:
https://dev.mysql.com/doc - look for & download 'connector-odbc-en.pdf' ======================== - print out duplex (about 100 pages)
https://www.unixODBC.org <-- download unixODBC-2.2.14.tar.gz ======================= - store in directory shown above
https://supportline.microfocus/Documentation/books/sx51ws02/sx51indx.htm ======================================================================== - look for the 'Data Base Access Guide' Part 2 OpenESQL (chapter 7)
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
You need to understand it before you can setup ODBC for COBOL OpenESQL access to MySQL DataBases. I recommend you print out & study the MySQL ODBC install guide 'connector-odbc-en.pdf'. The 3 most important items are:
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Assuming you have downloaded unixODBC-2.2.14.tar.gz from www.unixODBC.org and stored in the directories shown on page '2B1'
#1. Login as root
=============
#2. cd /home/mysql2 <-- cd to directories holding downloaded software
===============
#3. mkdir unixODBC <-- make subdir to unzip/untar the downloaded file
==============
#4. cp download/unixODBC-2.2.14.tar.gz unixODBC
===========================================
- copy downloaded file to subdir for unzip/untar
#5. gunzip unixODBC-2.2.14.tar.gz <-- unzip
=============================
#6. tar xvf unixODBC-2.2.14.tar <-- untar
===========================
#8a. uvlp12 README <-- print & study
=============
#8b. uvlp12 INSTALL <-- print & study
==============
#9. CFLAGS=-m32 configure --disable-gui
===================================
- configure for your system (mine is RHEL 5.1 on AMD Opteron 64)
- must configure 32 bit driver manager to match Micro Focus COBOL OpenESQL
#10. make <-- compile
====
#11. make install <-- install programs, data files,& documentation
============
| Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#12. llr /usr/local/ (alias llr='ls -lr | more')
===============
/usr/local: drwxr-xr-x 2 root root 4096 May 11 13:55 bin drwxr-xr-x 3 root root 4096 May 16 20:41 etc drwxr-xr-x 2 root root 4096 May 13 08:28 include drwxr-xr-x 2 root root 4096 May 11 13:55 lib drwxr-xr-x 2 root root 4096 Oct 10 2006 lib64 drwxr-xr-x 2 root root 4096 Oct 10 2006 libexec drwxr-xr-x 2 root root 4096 Oct 10 2006 sbin drwxr-xr-x 4 root root 4096 Feb 19 2008 share drwxr-xr-x 2 root root 4096 Oct 10 2006 src
/usr/local/bin: -rwxr-xr-x 1 oracle root 4143 Nov 17 16:25 coraenv -rwxr-xr-x 1 oracle root 2415 Nov 17 16:25 dbhome -rwxr-xr-x 1 root root 86618 May 11 13:55 dltest -rwxr-xr-x 1 root root 56954 May 11 13:55 isql -rwxr-xr-x 1 root root 47740 May 11 13:55 iusql -rwxr-xr-x 1 root root 13630 May 11 13:55 odbc_config -rwxr-xr-x 1 root root 85746 May 11 13:55 odbcinst -rwxr-xr-x 1 oracle root 4729 Nov 17 16:25 oraenv
/usr/local/etc: -rw-r--r-- 1 root root 761 May 16 20:41 odbc.ini <-- you must edit -rw-r--r-- 1 root root 682 May 16 20:41 odbcinst.ini <-- you must edit
/usr/local/include: -rw-r--r-- 1 root root 5511 May 11 13:55 autotest.h -rw-r--r-- 1 root root 10839 May 11 13:55 odbcinstext.h -rw-r--r-- 1 root root 18278 May 12 15:50 odbcinst.h -rw-r--r-- 1 root root 18707 May 12 15:58 odbcinst.h_modified -rw-r--r-- 1 root root 80416 May 11 13:55 sqlext.h -rw-r--r-- 1 root root 32070 May 11 13:55 sql.h -rw-r--r-- 1 root root 10862 May 11 13:55 sqltypes.h -rw-r--r-- 1 root root 21666 May 11 13:55 sqlucode.h -rw-r--r-- 1 root root 358 May 11 13:55 unixodbc_conf.h -rw-r--r-- 1 root root 2302 May 11 13:55 uodbc_extras.h -rw-r--r-- 1 root root 2591 May 11 13:55 uodbc_stats.h
/usr/local/lib:
lrwxrwxrwx 1 root root 19 May 11 13:55 libodbcmyS.so -> libodbcmyS.so.1.0.0
lrwxrwxrwx 1 root root 19 May 11 13:55 libodbcmyS.so.1 -> libodbcmyS.so.1.0.0
-rwxr-xr-x 1 root root 14170 May 11 13:55 libodbcmyS.so.1.0.0
... --- many files not shown ---
/usr/local/lib32: <-- 32 bit Drivers from MySQL (not unixODBC Driver Manager)
| Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Assuming you have downloaded mysql-connector-odbc...rpm from mysql.com and stored in the directories shown on page '2B1'
#1. Login as root
=============
#2. cd /home/mysql2/download <-- directories holding downloaded software
========================
| Note |
|
#3. rpm -i mysql-connector-odbc-3.51.27-0.x86_64.rpm
================================================
| Note |
|
#2. cd /home/mysql2
#3. mkdir myodbc351_32
#4. cp download/mysql-connector-odbc-3.51.27-linux-x86-32bit.tar.gz myodbc351_32
#4. cd myodbc351_32
#5. gunzip mysql-connector-odbc-3.51.27-linux-x86-32bit.tar.gz
==========================================================
#6. mkdir /usr/local/lib32
======================
#7. cp lib/* /usr/local/lib32
=========================
#8. ls -l /usr/local/lib32
======================
total 29484 -rwxr-xr-x 1 root root 4282487 May 21 07:14 libmyodbc3-3.51.27.so -rwxr-xr-x 1 root root 1050 May 21 07:14 libmyodbc3.la -rwxr-xr-x 1 root root 4296275 May 21 07:14 libmyodbc3_r-3.51.27.so -rwxr-xr-x 1 root root 1064 May 21 07:14 libmyodbc3_r.la -rwxr-xr-x 1 root root 4296275 May 21 07:14 libmyodbc3_r.so -rwxr-xr-x 1 root root 6469676 May 21 07:14 libmyodbc3S-3.51.27.so -rwxr-xr-x 1 root root 1050 May 21 07:14 libmyodbc3S.la -rwxr-xr-x 1 root root 4282487 May 21 07:14 libmyodbc3.so -rwxr-xr-x 1 root root 6469676 May 21 07:14 libmyodbc3S.so
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
You must setup the following 'export's in the profiles of users who wish to use ODBC:
#1. Login as mysql2
===============
#2. vi .bash_profile <-- edit profiles
================ - add following 'export's on the end
export ODBCINI=/usr/local/etc/odbc.ini # user DSN (Data Source Name) export ODBCSYSINI=/usr/local/etc # system file directory export LD_LIBRARY_PATH=/usr/local/lib32:/usr/local/lib:/usr/lib:$LD_LIBRARY_PATH
#1. Login root
==========
#2. cd /usr/local/etc
=================
#3. vi odbc.ini <-- edit ODBC 'user' config file
=========== - see result listed on next page
#4. vi odbcinst.ini <-- edit ODBC 'system' config file
=============== - see result listed on next page
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
# odbc.ini - setup by Owen Townsend, May 15/2009 # /usr/local/etc/odbc.ini <-- this file location # /usr/local/etc/odbcinst.ini <-- also see associated file # export ODBCINI=/usr/local/etc/odbc.ini <-- in profile # export ODBCSYSINI=/usr/local/etc <-- in profile # - see test/demos at www.uvsoftware.ca/sqldemo.htm#Part_2 # [myodbc351] Driver = /usr/local/lib32/libmyodbc3.so DATABASE = ar DESCRIPTION = 32 bit ODBC or 64 ? OPTION = 3 SERVER = localhost SOCKET = /tmp/mysql.sock PORT = 3306 USER = mysql2 PASSWORD = mysql200 # #May20/09 - DRIVER PROBLEM for COBOL 'cant open /usr/lib64/libmyodbc3.so' # - need 32 bit driver since Micro Focus COBOL OpenESQL is 32 bit only # - Drivers changed from /usr/lib64/... (rpm pkg) # to /usr/local/lib32/... 32 bit driver retrieved from tar.gz pkg # mysql-connector-odbc-3.51.27-linux-x86-32bit.tar.gz #May21/09 - 'DRIVER MANAGER' (from www.unixODBC.org) re-config for 32 bits # --> CFLAGS=-m32 ./configure --disable-gui + make + make install # - Now 32 bit isql works, BUT COBOL gets error # "cant connect to local MySQL server thru socket /tmp/mysql.sock" #May22/09 - 'SOCKET PROBLEM' fixed by changing MySQL socket to match OpenESQL # - change SOCKET=/var/lib/mysql/mysql.sock to SOCKET=/tmp/mysql.sock # - in /etc/my.cnf (MySQL system configuration file) # - see listing at www.uvsoftware.ca/sqldemo.htm#2E3
# odbcinst.ini - setup by Owen Townsend, May 15/2009 # /usr/local/etc/odbcinst.ini <-- this file location # /usr/local/etc/odbc.ini <-- also see associated file # export ODBCINI=/usr/local/etc/odbc.ini <-- in profile # export ODBCSYSINI=/usr/local/etc <-- in profile # - see test/demos at www.uvsoftware.ca/sqldemo.htm#Part_2 # # [MySQL ODBC 3.51 Driver] [myodbc351] Description = MySQL ODBC 3.51 Driver Driver = /usr/local/lib32/libmyodbc3.so Setup = /usr/local/lib32/libmyodbc3S.so Server = localhost UsageCount = 1 #May21/09 - not sure what odbcinst.ini is for ? # - I proved that Driver used is spcfd by odbc.ini # - not sure about others required ???
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
# my.cnf - MySQL "system configuration file" (stored at /etc/my.cnf) # - see MySQL demos at www.uvsoftware.ca/sqldemo.htm#2E3 # - also shows '.my.cnf' user config file (in user homedir) # [mysqld] datadir=/var/lib/mysql user=mysql # password=mysql00 #<-- password NOT accepted in this file socket=/tmp/mysql.sock # socket=/var/lib/mysql/mysql.sock # #May23/09/OT - setup /etc/my.cnf from /etc/my.cnf.rpmnew # - changed socket as above for COBOL/ODBC/MySQL (original below) # socket=/var/lib/mysql/mysql.sock #<-- did not work for COBOL # - also specify in [client] group (as well as server group [mysqld] above) # [client] socket=/tmp/mysql.sock # socket=/var/lib/mysql/mysql.sock #
# .my.cnf - "user configuration file" for userid mysql2 # - stored in user's home directory # - also see /etc/my.cnf "system configuration file" # - see MySQL demos at www.uvsoftware.ca/sqldemo.htm#2E3 [client] user=mysql2 password=mysql200 database=ar # #[mysqld] <-- see [mysqld] in /etc/my.cnf & $HOME/.my.cnf # socket=/var/lib/mysql/mysql.sock #<-- did not work for COBOL # socket=/tmp/mysql.sock #<-- works for COBOL # - changed socket as above for COBOL/ODBC/MySQL (original below)
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Before we attempt to execute a COBOL program to access MySQL via ODBC, we will test our ODBC setup using 'isql'.
'isql' allows us to enter SQL queries via ODBC, similar to the 'mysql' utility that allows us direct access to the MySQL databases.
#1. Login mysql2 --> /home/mysql2
============
#2. isql <-- omit arguments to display 'help' screen
====
isql DSN [UID] [PWD] [options] <-- command format from help screen
==============================
- 'DSN' (Data Source Name) must match the DSN coded in odbc.ini
- see [myodbc351] on 1st non#comment line in odbc.ini on page '2E2'
#3. isql myodbc351 <-- do not need to code UID & PWD
============== since they are specified in odbc.ini
- isql displays following & waits for your command:
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
+---------------------------------------+
#4. SQL> select * from cust1; <-- 'cust1' table created & loaded on page '1C3'
====================
+--------+----------------------+--------------------+----------------+-----+ | custno | name | address | city | prov| +--------+----------------------+--------------------+----------------+-----+ | 130140 | EVERGREEN MOTORS | 1815 BOWEN ROAD | NANAIMO | BC | | 139923 | JOHNSTONE BOILER | 1250 EAST PENDER | VANCOUVER | BC | | 150825 | RIGGERS INDUSTRIAL | 960 - 6TH AVENUE | HOPE | BC | | 201120 | ALLTYPE RENTAL LTD | BOX 1819 | DRAYTON | AL | | 308685 | FOOTHILLS ELECTRIC | 3932 3RD ST. NW | CALGARY | AL | | 315512 | PARTS PLUS | BOX 510 MAIN ST. | THREE HILLS | AB | | 400002 | ACKLANDS LTD | 945 -2ND AVE. | PRINCE GEORGE | BC | | 406082 | PRECAM RENTALS LTD | 10116-94TH AVE. | FORT ST. JOHN | BC | +--------+----------------------+--------------------+----------------+-----+ SQLRowCount returns 8 8 rows fetched
#5. SQL> quit
====
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Here are the subdirs/files we will need to test/demo COBOL & MySQL/ODBC: Please see Part_0 to download sqldemofiles.zip, unzip in tmp2/..., and copy desired files into your subdirs setup in /home/mysql2/...
/home/mysql2 :-----cbls <-- COBOL demo programs : :-----sqlmyo1.cbl : :-----sqlmyo2.cbl : :-----testcon1.cbl :-----cblx <-- PRO*COBOL & Micro Focus compiler outputs :-----cblis <-- PRO*COBOL listings :-----cblst <-- Micro Focus COBOL compiler listings :-----cpys <-- copybooks (sqlca.cob) : :-----cust1.cpy : :-----sqlca.cpy : ctl <-- control files : :-----cobdirectives : :-----extfh.cfg :-----dat1 <-- test data files (from UV Software) : :-----cust1.dat : :-----cust1.fix :-----sf <-- shell script Files :-----tmp <-- temp files (keep working directory clean) : :-----tmp2 <-- temp files (keep working directory clean) : :-----cbls : :-----... as above : :-----sf : :-----sqldemofiles.zip <-- download zip archive of test subdirs/files
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login mysql2 --> /home/mysql2
============
#2. mkdir cbls cblst cblx cpys ctl dat1 tmp tmp2
============================================
- make subdirs to hold demo files
#3a. Download www.uvsoftware.ca/sqldemofiles.zip into tmp2/... #3b. cd tmp2 #3c. unzip sqldemofiles.zip #3d. cd ..
#4a. cp tmp2/cbls/sqlmyo* cbls <-- COBOL programs for MySQL demo #4b. cp tmp2/cbls/testcon1.cbl cbls
#5. cp tmp2/cpys/* cpys <-- COBOL copybooks
#6. cp tmp2/ctl/* ctl <-- control files (COBOL directirves)
#7. cp tmp2/dat1/* dat1 <-- test data files to load tables
If you have Vancouver Utilities installed, you could copy directly from the uvadm/... subdirs to your /home/mysql2/... subdirs:
#4a. cp /home/uvadm/mf/cbls/sqlmyo* cbls <-- COBOL programs #4b. cp /home/uvadm/mf/cbls/testcon1.cbl cbls
#5a. cp /home/uvadm/mf/cpys/cust1.cpy <-- COBOL copybooks #5b. cp /home/uvadm/mf/cpys/sqlca.cpy
#6. cp /home/uvadm/ctl/cobdirectives ctl <-- COBOL Directives for compile
#7. cp /home/uvadm/dat1/cust1* dat1 <-- test data files
| Note |
|
#5b. cp $DB2DIR/include/cobol_mf/sqlca.cob cpys/sqlca.cpy
====================================================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Before compiling the COBOL programs you must activate the database option for MySQL/ODBC. You can see the entire ctl/cobdirectives file listed on page '4T1' but here are the database options (lines 22-32).
# ** Database Directives ** # ---------- uncomment 1 of the following & modify if required ---------- # -C SQL(targetdb==ORACLEOCI) # -C "DB2(init==prot db==ar)" -C SQL(dbman==ODBC) # -C "preprocess(cobsql) cobsqltype==ORACLE8 end-c comp5==yes endp" # # Use "preprocess(cobsql) ... to invoke ORACLE PRO*COBOL automatically # Better than running procobol separately prior to compile # animation then shows EXEC SQL stmts (vs many generated lines) # (NO directive required if calling ProCOBOL separately before compiler) # # OpenESQL targetdb= alternatives to above "preprocess(cobsql) ..." # could be DB2,INFORMIX,MSQLSERVER,SYBASE (not MySQL)
| Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login as mysql2 --> /home/mysql2
================================
#2. vi ctl/cobdirectives <-- activate '-C SQL(dbman==ODBC)'
==================== (see database alterantives listed above)
#3a. mfcbl1 sqlmyo1.cbl <-- compile program to load table
=================== - see sqlmyo1.cbl listed on page '5U3'
#3b. mfcbl1 sqlmyo2.cbl <-- compile program to unload table to seqntl file
===================
#4a. export CUST1IN=dat1/cust1.fix <-- export External-Name INput to sqlmyo1
=============================
#4b. export CUST1OUT=tmp/cust1.fix <-- export External-Name OUTput from sqlmyo2
=============================
#5a. cobrun cblx/sqlmyo1 <-- execute program to create/load cust1 table
====================
#5b. cobrun cblx/sqlmyo2 <-- execute program to unload cust1 table
====================
#6. cat tmp/cust1.fix <-- display output
=================
130140 EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC 139923 JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC 150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC 201120 ALLTYPE RENTAL LTD BOX 1819 DRAYTON AL 308685 FOOTHILLS ELECTRIC 3932 - 3A ST. NW CALGARY AL 315512 PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB 400002 ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC 406082 PRECAM RENTALS LTD 10116-94TH AVE. FORT ST. JOHN BC
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
identification division.
* sqlmyo1 - demo COBOL-API for MySQL/ODBC
* - insert customer Name&Address into table from text file
* - first drops (if exists) & recreates the table
* - also see sqlmyo2 to read table rows write seqntl file
* see doc at www.uvsoftware.ca/sqldemo.htm#Part_4
* sqlmyo1 alternate versions: sqlora1 for Oracle, sqldb21 for DB2
* see ctl/cobdirectives listed at SQLdemo.htm#4T1
* -C SQL(dbman==ODBC) #<-- for MySQL/ODBC
program-id. sqlmyo1.
environment division.
input-output section.
file-control.
select cust1 assign external cust1in
organization line sequential access mode sequential.
data division.
file section.
fd cust1 record contains 80 characters.
01 cust1rec.
05 c1num pic 9(6).
05 filler pic x(1).
05 c1name pic x(22).
05 filler pic x(1).
05 c1adrs pic x(22).
05 filler pic x(1).
05 c1city pic x(16).
05 filler pic x(1).
05 c1prov pic x(2).
05 filler pic x(7).
*
working-storage section.
* database options, communications area, & declare section
copy "sqlca.cpy".
exec sql begin declare section end-exec.
*
* database & user/pswd for connect to MySQL, Oracle,& DB2
01 dbname pic x(20).
01 userpass pic x(20).
*
* customer record fields to be inserted into cust1 table
01 csrec.
05 csnum pic 9(6).
05 csname pic x(22).
05 csadrs pic x(22).
05 cscity pic x(16).
05 csprov pic x(2).
exec sql end declare section end-exec.
*
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
procedure division.
begin-program.
**connect to database --> uncomment for MySQL/ODBC,Oracle,or DB2
* - modify cobdirectives, see www.uvsoftware.ca/sqldemo.htm#4T1
* following works for Oracle:
* move "demo1/demo100" to userpass.
* exec sql connect :userpass end-exec.
* following works for DB2
* move "ar" to dbname
* move "db2demo1" to dbuser
* move "db2demo100" to dbpass
* exec sql connect
* TO :dbname USER :dbuser USING :dbpass end-exec.
* following works for MySQL/ODBC
move "myodbc351" to dbname.
move "mysql2/mysql200" to userpass.
exec sql connect to :dbname user :userpass end-exec.
*
* drop table & recreate to clear any old table data
exec sql drop table cust1 end-exec.
if sqlcode not = 0 go to sql-error.
exec sql create table cust1
(custno numeric(6) primary key, name char(22),
adrs char(22), city char(16), prov char(2)) end-exec.
if sqlcode not = 0 go to sql-error.
*
* open input file & use loop to get records & insert to table
open input cust1.
mainloop.
read cust1 at end go to cust1eof.
move c1num to csnum, move c1name to csname,
move c1adrs to csadrs, move c1city to cscity,
move c1prov to csprov.
exec sql insert into cust1
(custno, name, adrs, city, prov) values
(:csnum, :csname, :csadrs, :cscity, :csprov) end-exec.
if sqlcode not = 0 go to sql-error.
go to mainloop.
*
* end of file
cust1eof.
display "EOF, cust1 table loaded" upon console.
exec sql commit work release end-exec.
close cust1. stop run.
*
* SQL error rtn - when any SQL error occurs
sql-error.
display "MySQL error detected: " SQLERRMC upon console.
exec sql rollback work release end-exec.
stop run.
******************* end program sqlmyo1.cbl *********************
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
identification division.
* sqlmyo2 - demo COBOL-API for MySQL/ODBC
* - read cust1 table & write to a sequential file
* (vs sqlora1 program to create table & load from file)
* - also see sqlmyo2 to read table rows write seqntl file
* see doc at www.uvsoftware.ca/sqldemo.htm#Part_4
* sqlmyo2 alternate versions: sqlora2 for Oracle, sqldb22 for DB2
* see ctl/cobdirectives listed at SQLdemo.htm#4T1
* -C SQL(dbman==ODBC) #<-- for MySQL/ODBC
program-id. sqlmyo2.
environment division.
input-output section.
file-control.
select cust1 assign external cust1out
organization line sequential access mode sequential.
data division.
file section.
fd cust1 record contains 80 characters.
01 cust1rec.
05 c1num pic 9(6).
05 filler pic x(1).
05 c1name pic x(22).
05 filler pic x(1).
05 c1adrs pic x(22).
05 filler pic x(1).
05 c1city pic x(16).
05 filler pic x(1).
05 c1prov pic x(2).
05 filler pic x(7).
*
working-storage section.
* database options, communications area, & declare section
* copy "sqlca.cpy".
* above copy *cmtd out, mystery how following include works ?
exec sql include sqlca end-exec.
exec sql begin declare section end-exec.
*
* database & user/pswd for connect to MySQL, Oracle,& DB2
01 dbname pic x(20).
01 userpass pic x(20).
*
* customer record fields to be inserted into cust1 table
01 csrec.
05 csnum pic 9(6).
05 csname pic x(22).
05 csadrs pic x(22).
05 cscity pic x(16).
05 csprov pic x(2).
exec sql end declare section end-exec.
*
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
procedure division.
begin-program.
**connect to database --> uncomment for MySQL/ODBC,Oracle,or DB2
* - modify cobdirectives, see www.uvsoftware.ca/sqldemo.htm#4T1
* following works for Oracle:
* move "demo1/demo100" to userpass.
* exec sql connect :userpass end-exec.
* following works for DB2
* move "ar" to dbname
* move "db2demo1" to dbuser
* move "db2demo100" to dbpass
* exec sql connect
* TO :dbname USER :dbuser USING :dbpass end-exec.
* following works for MySQL/ODBC
move "myodbc351" to dbname.
move "mysql2/mysql200" to userpass.
exec sql connect to :dbname user :userpass end-exec.
*
* declare cursor & select cust1 fields for fetch
exec sql declare cust1cursor cursor for select
custno, name, adrs, city, prov from cust1 end-exec.
if sqlcode not = 0 go to sql-error.
exec sql open cust1cursor end-exec.
if sqlcode not = 0 go to sql-error.
*
* open output file & use loop to fetch rows from table & write
open output cust1.
mainloop.
exec sql fetch cust1cursor into
:csnum, :csname, :csadrs, :cscity, :csprov end-exec.
if sqlcode not = 0 go to table-end.
* move host variables to FD record fields & write record
move csnum to c1num, move csname to c1name,
move csadrs to c1adrs, move cscity to c1city,
move csprov to c1prov.
write cust1rec.
go to mainloop.
*
* end of table
table-end.
display "End of table, all rows written to output file"
upon console.
exec sql commit work release end-exec.
close cust1. stop run.
*
* SQL error rtn - when any SQL error occurs
sql-error.
display "MySQL error detected: " SQLERRMC upon console.
exec sql rollback work release end-exec.
stop run.
******************* end program sqlmyo2.cbl *********************
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
* testcon1.cbl - MySQL/ODBC CONNECT test program
* - original /opt/microfocus/mf/demo/openesql/testconn.cbl
* SQL(dbman==ODBC) #<-- COBOPT ctl/cobdirectives for MySQL/ODBC
working-storage section.
* Include the SQL Communications Area. This includes the
* definitions of SQLCODE, etc
*EXEC SQL INCLUDE SQLCA END-EXEC
copy "sqlca.cpy".
EXEC SQL BEGIN DECLARE SECTION END-EXEC
01 svr pic x(32).
01 usr pic x(32).
01 pass pic x(32).
01 usr-pass pic x(64).
EXEC SQL END DECLARE SECTION END-EXEC.
procedure division.
display "MySQL/ODBC Connect Test: solicit DSN, user,& passwd"
display "demo: DSN=myodbc351, user=mysql2, passwd=mysql2pw"
display "Enter data source name --> " with no advancing
accept svr
display "Enter username --> " with no advancing
accept usr
display "Enter password --> " with no advancing
accept pass.
string usr delimited space "." delimited size
pass delimited space into usr-pass.
EXEC SQL CONNECT TO :svr USER :usr USING :pass END-EXEC.
* ========================================================
if sqlcode not = 0
display "MySQL/ODBC connect test failed"
display sqlcode " " sqlerrmc
stop run.
EXEC SQL DISCONNECT CURRENT END-EXEC.
if sqlcode not = 0
display "Error: cannot disconnect "
display sqlcode " " sqlerrmc
stop run.
display "MySQL/ODBC connect test OK"
stop run.
************** end testcon1.cbl (modified by OT) ***************
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
I was trying to use Micro Focus COBOL OpenESQL and ODBC to access MySQL RDBMS, and had some initial problems, which have been fixed as described here.
#9. So I changed the odbc.ini file (see page '2E2')
SOCKET = /var/lib/mysql/mysql.sock #<-- original odbc.ini coding
SOCKET = /tmp/mysql.sock #<-- tried this to fix error
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
| 3A1. | Preparation to install Oracle |
| - setup user 'oracle' & group 'oinstall' | |
| - setup profile (ORACLE_BASE,HOME,OWNER,SID,& PATH) |
| 3A2. | Download Oracle server & client files |
| - ORACLE_BASE & ORACLE_HOME dirs illustrated |
| 3A3. | Install Oracle DATABASE SERVER & Oracle CLIENT |
| - using Oracle 'runInstaller' |
| 3A4. | Post install procedures |
| - run SQL*Plus to startup database server |
| 3B1. | Oracle Enterprise Manager Database Control (optional) |
| 3C1. | Setup Unix/Linux user 'demo1' for SQL test/demo |
| - using unix/linux root 'adduser' | |
| Setup Oracle USER 'demo1' for SQL test/demo | |
| - using Oracle SYSDBA, SQL*Plus,& 'create user' |
| 3C2. | Setup subdirs & download test files for SQL demo |
| (sqlora1.cbl,sqlora2.cbl,cust1.sql,cust1.ctl,cust1.txt,cust1.fix,mfprocob1) |
| 3D1. | Oracle SQL demo - CREATE & LOAD table |
| - create table with SQL*Plus |
| 3D2. | Generate batch-files to create & load Oracle tables |
| 3D3. | listings of batch-file to create table & control-file to load table |
| 3D4. | generating uvcopy job to convert fixed-field to pipe|delimited |
| for loading SQL tables |
| 3D5. | Executing job to convert fixed-field to pipe|delimited to load SQL table |
| 3D6. | Commands to use generated batch-files to create & load tables |
| using SQL*Plus to verify table loaded OK ('select * from cust1') |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Here are the procedures I used to download & install Oracle 11g database server & client. My main intention was to test PRO*COBOL.
The first task was to setup a unix/linux user 'oracle' with group 'oinstall' as required by Oracle install. Mount point /h22 had 35 gig available & 20 gig was used after install server & client (& setup 1 demo user & 1 table).
#1. Login root
==========
#2. mkdir /h22/oracle <-- make homedir for oracle user (ORACLE_BASE)
================= --> export ORACLE_BASE=/home/oracle <-- in profiles
#3. adduser -m -g oinstall -d /h22/oracle oracle
============================================
#4. passwd oraclepw <-- set password
===============
#5. setup profiles for Oracle. See suggested profiles at
https://www.uvsoftware.ca/admjobs.htm#Part_1
#5a. vi /home/uvadm/env/common_profile <-- add ORACLE OWNER,DID,BASE,& HOME
================================= & PATH to bin, etc
#5b. vi /home/uvadm/env/stub_profile <-- will be copied to oracle homedir
=================================
#5c. cp -p /home/uvadm/env/stub_profile /home/oracle/.bash_profile
=============================================================
export ORACLE_OWNER=oracle
export ORACLE_SID=demo1
export ORACLE_BASE=/h22/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_2 #<-- unset for install
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORACLE_HOME/lib32
alias cdoh='cd $ORACLE_HOME' # alias for quick cd to $ORACLE_HOME
| Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
https://www.oracle.com/technology/software/products/database/index.htm =====================================================================
Download the following database & client zip files into /h22/oracle:
/h22/oracle/linux.x64_11gR1_database_1013.zip =============================================
/h22/oracle/linux.x64_11gR1_client.zip =======================================
| Note |
|
/h22/oracle <-- ORACLE_BASE :-----admin :-----cfgtoollogs :-----client <-- CLIENT unzip/install staging area : :-----doc : :-----install : :-----stage :-----database : :-----doc : :-----install : :-----stage <-- DATABASE SERVER unzip/install staging area :-----oradata :-----oraInventory :-----product <-- ORACLE_HOME : :-----11.1.0 : : :-----db_2 : : : :-----apex : : : :-----bin : : : :-----cdata : : : :-----config : : : :-----lib : : : :-----lib32 : : : :-----log : : : :-----mesg : : : :-----network : : : :-----rdbms
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login oracle --> /h22/oracle (ORACLE_BASE)
============
#2. unzip /h22/oracle/linux.x64_11gR1_database_1013.zip
===================================================
- unzips files into $ORACLE_BASE/database (see prior page)
#3. cd database <-- change into database directory
===========
#4. unset ORACLE_HOME <-- unset, install determines & informs
=================
#5. startx <-- start X windows (runInstaller is a GUI)
======
#6. ./runInstaller <-- execute Installer for Oracle DATABASE SERVER
==============
#7. vi /home/uvadm/env/common_profile <-- update profiles
=================================
- update ORACLE_HOME as determined by runInstaller
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_2
====================================================
#8. exit <-- exit, for log back in, to setup profile ORACLE PATHs
====
#1. Login oracle --> /h22/oracle (ORACLE_BASE)
============
#2. unzip /h22/oracle/linux.x64_11gR1_client.zip
============================================
- unzips files into $ORACLE_BASE/client (see prior page)
#3. cd client <-- change into database directory
=========
#4. startx <-- start X windows (runInstaller is a GUI)
======
#5. ./runInstaller <-- execute Installer for Oracle CLIENT
==============
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login oracle --> /h22/oracle (ORACLE_BASE)
============
#2. sqlplus "/ as SYSDBA" <-- start SQL*Plus
=====================
#3. SQL> startup <-- start database server
=======
#4. SQL> @?/rdbms/admin/utlrp.sql <-- recompile oracle PL/SQL modules
======================== (recommended by Oracle)
#5. exit <-- exit back to unix/linux prompt
====
#6. $ORACLE_HOME/bin/genclntst <-- generate client static library
==========================
#6a. genclntst <-- same as above (ORACLE_HOME/bin is in the profile PATH)
=========
#7. sqlplus "/ as SYSDBA" <-- start SQL*Plus
=====================
#8. SQL> shutdown <-- shutdown Oracle
========
#9. exit <-- exit from 'oracle' admin login
====
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
'Oracle Enterprise Manager Database Control' is a GUI management tool for Oracle. It runs under an internet browser (Firefox or Internet Explorer). It is optional, since you can do the same things using SQL*Plus from a character screen.
#1. Login oracle --> /h22/oracle (ORACLE_BASE)
============
#2. lsnrctl start <-- start the 'listener' (required for browser interface)
=============
#3. emctl start dbconsole <-- start Enterprise Manager
=====================
#4a. Switch to a GUI screen to run the browser
| #4b. | https://localhost:1158/em <-- connect to Enterprise Manager |
| ========================= |
| Note |
|
#5a. Login user ------> sys #5b. Login password --> oracle #5c. Login as --------> sysdba
| Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login root
==========
#2. adduser -m -g apps -d /home/demo1 demo1
=======================================
#3. passwd demo1 - set password 'demo1pw'
============
#4. cp -p /home/uvadm/env/stub_profile /home/demo1/.bash_profile
============================================================
- suggested profiles at https://www.uvsoftware.ca/admjobs.htm#Part_1
#1. Login oracle --> /h22/oracle (ORACLE_BASE)
============
#2. sqlplus "/ as SYSDBA" <-- start SQL*Plus
=====================
#3. SQL> startup <-- start database server
=======
#4. SQL> create user demo1 identified by demo1pw
=======================================
default tablespace example quota 5M on example;
===============================================
#5. SQL> grant all privileges to demo1;
==============================
#6. SQL> exit
====
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
The test/demo files are provided on the web site in zip file 'sqldemofiles.zip'. Please see Part_0 for the instructions to download, unzip,& copy to the subdirs shown below:
/home/demo1 :-----cbls <-- COBOL demo programs : :-----sqlora1.cbl : :-----sqlora2.cbl :-----cblx <-- PRO*COBOL & Micro Focus compiler outputs :-----cblis <-- PRO*COBOL listings :-----cblst <-- Micro Focus COBOL compiler listings :-----cpys <-- copybooks (sqlca.cob) : :-----cust1.cpy : :-----sqlca.cpy :-----dat1 <-- test data files (from UV Software) : :-----cust1.txt : :-----cust1.fix :-----maps <-- record layouts generated from copybooks :-----pfp1 <-- jobs to convert fixed-field to pipe|delimited :-----sf <-- shell script Files : :-----mfprocob1 :-----sqlTC1 <-- batch-files to Create SQL Tables : :-----cust1.sql :-----sqlTL1 <-- batch-files to Load Tables : :-----cust1.ctl :-----tmp <-- temp files (keep working directory clean)
| Note |
|
cp $ORACLE_HOME/precomp/public/sqlca.cob cpys/sqlca.cpy =======================================================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
/home/demo1 :-----cbls : :-----sqlora1.cbl <-- COBOL to INSERT rows from fixed field file : :-----sqlora2.cbl <-- COBOL to SELECT rows into a fixed field file :-----cblx : :-----sqlora1.cob <-- PRO*COBOL precompiler output : :-----sqlora1.int <-- Micro Focus compiler output : :-----sqlora1.cbl <-- COBOL source (copied to cblx for animation) :-----cpys : :-----cust1.cpy <-- customer "delimited","file" INPUT to SQLLDR :-----dat1 : :-----cust1.txt <-- customer "delimited","file" INPUT to SQLLDR : :-----cust1.fix <-- customer fixed file INPUT to sqlora1 COBOL :-----maps : :-----cust1 <-- record layout generated from copybook :-----pfp1 : :-----cust1 <-- job to convert fixed-field to pipe|delimited :-----sf : :-----mfprocob1 <-- script to precompile/compile COBOL programs :-----sqlTC1 : :-----cust1.sql <-- SQL batch-file to CREATE cust1 table :-----sqlTC1 : :-----cust1.ctl <-- SQL control file to LOAD cust1 table :-----tmp : :-----cust1.fix <-- customer N&A fixed file OUTPUT from sqlora2
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Startup Oracle if not already running:
#1. Login oracle --> /h22/oracle (ORACLE_BASE)
============
#2. sqlplus "/ as SYSDBA" <-- start SQL*Plus
=====================
#3. SQL> startup <-- start database server
=======
#1. Login demo1 --> /home/demo1
===========
#2. sqlplus demo1/demo1pw <-- start SQL*Plus
=====================
#3. SQL> create table cust1 (custno numeric(6) primary key, name char(22)
================================================================
,adrs char(22), city char(16), prov char(2));
=============================================
#4. select table_name from user_tables;
===================================
- confirm table 'cust1' was created
#5. SQL> describe cust1
==============
Name Null? Type
---------------------------------------------
CUSTNO NOT NULL NUMBER(6)
NAME CHAR(22)
ADRS CHAR(22)
CITY CHAR(16)
PROV CHAR(2)
#6. SQL> exit <-- exit to unix shell to run SQLLDR
====
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Creating tables manually (as shown above) would be very laborious for files with many fields. UV Software provides uvcopy job 'sqlcreate1' to automatically generate an Oracle batch-file to create the table. The job also generates the SQL*LOADER control file to load the table. See uvcopy job 'sqlcreate1' and script 'sqlcreateA' fully documented in DATAcnv1.htm#Part_4.
#1. uvcopy cobmap1,fili1=cpys/cust1.cpy,filo1=maps/cust1
====================================================
- convert copybook to 'cobmap' (record layout)
#2. uvcopy sqlcreate1,fili1=maps/cust1,filo1=sqlTC1/cust1.sql
================================================================
,filo2=sqlTL1/cust1.ctl
=================================================================
- convert cobmap to batch-file & control-file to create & load table
- - - OR use script 'sqlcreateA' (combines above into 1 short command)
#2a. sqlcreateA cust1 <-- script equivalent of above (shorter command)
================
cobmap1 start-end bytes for cobol record fields 200903201409 pg# 0001
cpys/cust1.cpy cust1record RCSZ=00080 bgn end lth typ
* cust1.cpy - cobol copy book for cust1 file (oracle/sql demo)
01 cust1record.
10 cm-num pic 9(6). 0000 0005 006 n 6
10 filler001 pic x(1). 0006 0006 001
10 cm-name pic x(22). 0007 0028 022
10 filler002 pic x. 0029 0029 001
10 cm-adrs pic x(22). 0030 0051 022
10 filler003 pic x. 0052 0052 001
10 cm-city pic x(16). 0053 0068 016
10 filler004 pic x. 0069 0069 001
10 cm-prov pic x(2). 0070 0071 002
10 filler005 pic x(8). 0072 0079 008
*RCSZ=00080
See the output files listed on the next page --->
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
script 'sqlcreatA' is the easy way to generate batch-files to create & load tables from COBOL copybooks. The script runs cobmap1 to create a record layout & then runs 'sqlcreate1' to generate both the table-create & table-load batch files from the 'cobmap'.
See the demo copybook/map 'cust1' on the previous page. Here is the 'sqlcreateA' command followed by the 2 output batch-files to create & load the cust1 table.
sqlcreateA cust1.cpy <-- generate batch-files to create & load RDBMS Table ====================
/* cust1.sql - SQL batch file to create a table */ /* - generated from COBOL copybook 20090419:141601*/ /* www.uvsoftware.ca/datacnv1.htm#Part_4 & sqldemo.htm#Part_3 */ /* */ /* sqlplus user/pass @sqlTC2/cust1.sql */ /* ====================================== */ /* */ DROP TABLE cust1; CREATE TABLE cust1 ( cm_num number (06) , --#001 9(6). cm_name char (0022) , --#002 x(22). cm_adrs char (0022) , --#003 x(22). cm_city char (0016) , --#004 x(16). cm_prov char (0002) --#005 x(2). ); exit;
-- cust1.ctl - SQL*LOADER control file to load Oracle table -- - generated from COBOL copybook 20090419:141601 -- www.uvsoftware.ca/datacnv1.htm#Part_4 & sqldemo.htm#Part_3 -- -- sqlldr user/pass control=sqlTL2/cust1.ctl -- ============================================ -- load data infile 'dat1/cust1.dat' into table cust1 fields terminated by '|' optionally enclosed by '"' ( cm_num , --number (06) , --#001 9(6). cm_name , --char (0022) , --#002 x(22). cm_adrs , --char (0022) , --#003 x(22). cm_city , --char (0016) , --#004 x(16). cm_prov --char (0002) --#005 x(2). )
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Before we can load the SQL table, we must convert the data file from fixed-field to the the pipe|delimited|format expected by SQL LOADER. We can generate a uvcopy job to do this from the COBOL copybook as follows:
#1. uvcopy cobmap1,fili1=cpys/cust1.cpy,filo1=maps/cust1
====================================================
- convert copybook to 'cobmap' (record layout)
#2. uvcopy genpipe1,fili1=maps/cust1,filo1=pfp1/cust1
=================================================
- convert cobmap to uvcopy job to convert fixed-field to pipe-delimited
- - - OR use script 'genpipeA' (combines above into 1 short command)
#2a. genpipeA cust1 <-- script equivalent of above (shorter command)
==============
# cust1 - uvcopy job to convert EBCDIC/ASCII data to delimited text file
opr='cust1 - uvcopy code generated from copybook: cust1 '
rop=j200000r1 #Run OPtions: increase instrn storage & prompt display output
was=a33000b33000c999000d33000e33000
fili1=?d2asc/cust1,rcs=00080,typ=RSTm10000
filo1=?dat1/cust1.dat,rcs=9000,typ=LSTtd3 #<-- DOS option d3 for CR/LF
@run
opn all
loop get fili1,a0
skp> eof
# area a input, see 'get' instrn
# area b translated to ASCII, in case mainframe EBCDIC file (with packed?)
# area c data fields fixed 100 bytes apart in prep for var | delimit instrn
# area d output, see 'put' instrn at end
mvc b0(00080),a0 move input area a to area b
mvc c0(6),b0(6) #1 cm-num
mvc c100(22),b7(22) #2 cm-name
mvc c200(22),b30(22) #3 cm-adrs
mvc c300(16),b53(16) #4 cm-city
mvc c400(2),b70(2) #5 cm-prov
var d0(32000),c0(100),0005,'|'
trt d0(32000),$trtchr
clr c0(000500),' '
put1 put filo1,d0
skp loop
eof cls all
eoj
@pf2=genpipe1.sub
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Before we can load the SQL table, we must convert the data file from fixed-field to the pipe|delimited|format expected by SQL LOADER.
uvcopy pfp1/cust1,fili1=dat1/cust1.fix,filo1=dat1/cust1.txt ===========================================================
130140 EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC 139923 JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC 150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC 201120 ALLTYPE RENTAL LTD BOX 1819 DRAYTON AL 308685 FOOTHILLS ELECTRIC 3932 - 3A ST. NW CALGARY AL 315512 PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB 400002 ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC 406082 PRECAM RENTALS LTD 10116-94TH AVE. FORT ST. JOHN BC
130140|EVERGREEN MOTORS|1815 BOWEN ROAD|NANAIMO|BC|
139923|JOHNSTONE BOILER|1250 EAST PENDER|VANCOUVER|BC|
150825|RIGGERS INDUSTRIAL|960 - 6TH AVENUE|HOPE|BC|
201120|ALLTYPE RENTAL LTD|BOX 1819|DRAYTON|AL|
308685|FOOTHILLS ELECTRIC|3932 - 3A ST. NW|CALGARY|AL|
315512|PARTS PLUS|BOX 510 MAIN ST.|THREE HILLS|AB|
400002|ACKLANDS LTD|945 -2ND AVE.|PRINCE GEORGE|BC|
406082|PRECAM RENTALS LTD|10116-94TH AVE.|FORT ST. JOHN|BC|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
The previous page generated batch-files to create & load the cust1 table. We will now use 'sqlplus' to create the table via batch-file 'cust1.sql', followed by 'sqlldr' to load the table via batch-file 'cust1.ctl'.
#1. Login demo1 --> /home/demo1
===========
#2. sqlplus user/pass @sqlTC1/cust1.sql
===================================
- run sqlplus with 'batch-file' to CREATE the table
#3. sqlldr user/pass control=sqlTL1/cust1.ctl
=========================================
- run SQL*LOADER with 'control-file' to LOAD the table
- see batch-files listed on page '3D3'
- may have to change INFILE from d4pipe/cust1.dat to dat1/cust1.dat
#4. sqlplus demo1/demo1pw <-- start SQL*Plus
=====================
#5. SQL> select * from cust1 <-- SELECT all rows/fields from table
===================
CUSTNO NAME ADRS CITY PR
------ ---------------------- ---------------------- ---------------- --
130140 EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC
139923 JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC
150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC
201120 ALLTYPE RENTAL LTD BOX 1819 DRAYTON AL
308685 FOOTHILLS ELECTRIC 3932 3RD ST. NW CALGARY AL
315512 PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB
400002 ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC
406082 PRECAM RENTALS LTD 10116-94TH AVE. FORT ST. JOHN BC
8 rows selected.
/* select_all_cust1.sql - batch file to select all rows from cust1 */ /* - batch files useful for complex repeated selects */ select * from cust1; exit;
You could use the batch-file listed above as an alternative to logging in to SQL*PLUS to list the table rows:
#6. SQL> exit <-- exit from SQL*PLUS (back to Unix/Linux prompt)
#7. sqlplus user/pass @sqls/select_all_cust1.sql
============================================
- run SQL 'batch-file' to create the table
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
| 4A1. | Download COBOL programs & compile script |
| store program in /home/demo1/src/sqlora1.cbl & sqlora2.cbl | |
| store script in /home/demo1/sf/mfprocob1 |
| 4B1. | Setup COBOL compiler DIRECTIVES for ProCOBOL (1st of 3 compile modes) |
| - ProCOBOL run separately from compile requires NO directives | |
| - will later uncomment directives for (COBSQL) & (targetdb=OracleOCI) |
| 4C1. | Instructions to compile & execute demo program sqlora1.cbl |
| - using script mfprocob1 which runs preprocessor procobol before compile | |
| - sqlora1 connects to the database, creates the 'cust1' table, | |
| & loads it by INSERTing a row at a time from a fixed field file |
| 4C2. | Instructions to compile & execute demo program sqlora2.cbl |
| - sqlora2 SELECTs all rows from the 'cust1' table, | |
| converts to fixed field format & writes to a sequential file. |
| 4D1. | same as above, but using COBSQL directive to integrate PROCOBOL & compile |
| (advantage - animation sees only EXEC SQL stmt vs voluminous expansion) |
| 4E1. | same as above, but using SQL(targetdb==ORACLEOCI) directive |
| - does not require PROCOBOL, uses Micro Focus OpenESQL |
| 4F1. | same as above, but using OpenSQL & ODBC |
| - ODBC required for 'targetdb's other than ORACLEOCI | |
| - not yet provided for Oracle (ODBC is used for MySQL in Part_2) |
| 4S1. | script 'mfprocob1' - separate PROCOBOL preprocessor & COBOL compile |
| 4S2. | script 'mfcbl1' - COBSQL option to integrat PROCOBOL into compile |
| 4T1. | Micro Focus DIRECTIVES file suggested by UV Sofware |
| export COBOPT=$RUNLIBS/ctl/cobdirectives <-- invoked by above scripts |
| 4U1. | sqlora1.cbl - create table & INSERT rows from a sequential file |
| 4U2. | sqlora1a.cbl - same, but EXEC SQL WHENEVER *commented out |
| (not supported by ORACLEOCI) |
| 4V1. | sqlora2.cbl - FETCH all rows from table & write to a sequential file |
| 4V2. | sqlora2.cob - PRO*COBOL expanded code for compile by Micro Focus SX |
| - in case you are interested (do not normally need this) |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
The test/demo files are provided on the web site in zip file 'sqldemofiles.zip'. Please see Part_0 for the instructions to download, unzip,& copy to the subdirs shown below:
/home/demo1 :-----cbls : :-----sqlora1.cbl <-- COBOL program to INSERT rows from fixed field file : :-----sqlora2.cbl <-- COBOL program to SELECT rows & write to fixed file : : - COBOL programs listed begining on page '4U1' :-----cblx : :-----sqlora1.cob <-- PRO*COBOL output : :-----sqlora1.int <-- compiled interpretive code for debug/animation : :-----sqlora2.cob : :-----sqlora2.int :-----dat1 : :-----cust1.fix <-- fixed file INPUT to sqlora1 to load cust1 table :-----sf : :-----mfprocob1 <-- script to compile COBOL programs for PRO*COBOL : :-----mfcbl1 <-- script to compile COBOL programs for ORACLEOCI : : - scripts listed begining on page '4S1' :-----tmp : :-----cust1.fix <-- OUTPUT from sqlora2 : : - see cust1.fix listed on page '1C1'
#1. Login oracle --> /h22/oracle (ORACLE_BASE)
============
#2. sqlplus "/ as SYSDBA" <-- start SQL*Plus
=====================
#3. SQL> startup <-- start database server
=======
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
We will demo COBOL access to Oracle using 3 different interfaces as follows:
4C1. ProCOBOL run separately prior to COBOL compile script mfprocob1
- requires 'NO' DIRECTIVES
4D1. ProCOBOL integrated into COBOL compile (using usual script mfcbl1)
- requires 'preprocess(COBSQL)' directive.
4E1. OCI interface built in to Micro Focus COBOL
- requires 'SQL(targetdb==OracleOCI)' directive
4F1. OpenSQL using ODBC (required for 'targetdb's other than OracleOCI)
- not yet provided for Oracle (ODBC is used for MySQL in Part_2)
Here are lines 22-32 of the COBOL DIRECTIVES file stored in ctl/cobdirectives. See complete file listed on page '4T1'. You must uncomment the appropriate line before you compile the programs to demo the various interdace methods.
# ** Database Directives ** # ---------- uncomment 1 of the following & modify if required ---------- # -C SQL(targetdb==ORACLEOCI) # -C "DB2(init==prot db==ar)" # -C SQL(dbman==ODBC) # -C "preprocess(cobsql) cobsqltype==ORACLE8 end-c comp5==yes endp" # # Use "preprocess(cobsql) ... to invoke ORACLE PRO*COBOL automatically # Better than running procobol separately prior to compile # animation then shows EXEC SQL stmts (vs many generated lines) # (NO directive required if calling ProCOBOL separately before compiler) # # OpenESQL targetdb= alternatives to above "preprocess(cobsql) ..." # could be DB2,INFORMIX,MSQLSERVER,SYBASE (not MySQL)
| Note |
|
I specify DIRECTIVES to the Micro Focus COBOL compiler using the 'COBOPT' environmental variable specified in the compile script 'mfcbl1' as follows:
export COBOPT=$cwd/ctl/cobdirectives ==================================== - used if $cwd (Current Working Directory) contains file ./ctl/cobdirectives
export COBOPT=$UV/ctl/cobdirectives =================================== - used if $cwd does NOT contain file ./ctl/cobdirectives - $UV is /home/uvadm/
See cobdirectives listed on page '4T1' or at https://uvsoftware.ca/mvscobol.htm#4D1
See compile script 'mfcbl1' listed on page '4S2' or at https://uvsoftware.ca/mvscobol.htm#4E1
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Demo program 'sqlora1.cbl' (listed at '4U1') will connect to database create the 'cust1' table, & load it by INSERTing a row at a time from a fixed field file.
#1. Login as demo1 --> /home/demo1
================================
#2. vi ctl/cobdirectives <-- ensure directives set for ProCOBOL ?
==================== - NO database directives required for ProCOBOL
- see alternatives on page above
#3. mfprocob1 sqlora1.cbl <-- compile the COBOL Oracle demo program
===================== - see mfprocob1 script listed on page '4S1'
#4. export CUST1IN=dat1/cust1.fix <-- export COBOL External-Name to filename
==============================
#5. export COBSW="+A" <-- optional for debug (animate)
=================
#6. rtsora cblx/sqlora1 <-- execute program to create/load cust1 table
=====================
#7. sqlplus user/pass <-- start SQL*Plus
=================
#8. SQL> select * from cust1 <-- confirm table loaded
=================== - lists all table rows on screen
130140 EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC 139923 JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC 150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC 201120 ALLTYPE RENTAL LTD BOX 1819 DRAYTON AL 308685 FOOTHILLS ELECTRIC 3932 - 3A ST. NW CALGARY AL 315512 PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB 400002 ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC 406082 PRECAM RENTALS LTD 10116-94TH AVE. FORT ST. JOHN BC
| Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Demo program 'sqlora2.cbl' (listed at '4V1') will connect to database, SELECT all rows from the 'cust1' table, convert to fixed field format & write to a sequential output file.
We assume that you have already created & loaded the 'cust1' table by executing 'sqlora1' (above), or manually as on pages '1C3'+ or by batch file script 'createcust1b' on page '1D1'.
#1. Login as demo1 --> /home/demo1
================================
#2. mfprocob1 sqlora2.cbl <-- compile the COBOL Oracle demo program
===================== - see mfprocob1 script listed on page '4S1'
#3. export CUST1OUT=tmp/cust1.fix <-- export COBOL External-Name to filename
=============================
#4. export COBSW="+A" <-- optional for debug (animate)
=================
#5. rtsora32 cblx/sqlora2 <-- execute program to read table & write file
=====================
#6. cat tmp/cust1.fix <-- confirm table unloaded into sequential file
================= - 'cat' lists all table rows on screen
130140 EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC 139923 JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC 150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC 201120 ALLTYPE RENTAL LTD BOX 1819 DRAYTON AL 308685 FOOTHILLS ELECTRIC 3932 - 3A ST. NW CALGARY AL 315512 PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB 400002 ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC 406082 PRECAM RENTALS LTD 10116-94TH AVE. FORT ST. JOHN BC
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
The 'mfprocobol' compile script (used on the previous pages) executed Oracle's procobol precompiler separately & preceding the Micro Focus COBOL compile.
The disadvantage of separate preprocessing & compile is that animation will see all the voluminous code generated by the procobol precompiler.
It is much better to use the Micro Focus 'COBSQL' option which integrates procobol into 1 compile execution. Animation will then show only the original 'EXEC SQL' stmtns & not the voluminous expanded code. COBSQL is invoked by the following COBOL directive:
-C "preprocess(cobsql) cobsqltype==ORACLE8 end-c comp5==yes endp" =================================================================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login as demo1 --> /home/demo1
================================
#2. vi ctl/cobdirectives <-- set directive for 'preprocess(COBSQL)'
==================== - see above & discussion on page '4B1'
#3a. mfcbl1 sqlora1.cbl <-- compile program to load table
=================== - using script 'mfcbl1' vs 'mfprocob1'
- see mfcbl1 listed on page '4S2'
#3b. mfcbl1 sqlora2.cbl <-- compile program to unload table to seqntl file
===================
#4a. export CUST1IN=dat1/cust1.fix <-- export External-Name INput to sqlora1
=============================
#4b. export CUST1OUT=tmp/cust1.fix <-- export External-Name OUTput from sqlora2
=============================
#5. export COBSW="+A" <-- optional for debug (animate)
=================
#6a. rtsora32 cblx/sqlora1 <-- execute program to create/load cust1 table
=====================
#6b. rtsora32 cblx/sqlora2 <-- execute program to create/load cust1 table
=====================
#7. sqlplus user/pass <-- start SQL*Plus
=================
#8. SQL> select * from cust1 <-- confirm table loaded
=================== - lists all table rows on screen
130140 EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC 139923 JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC 150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC 201120 ALLTYPE RENTAL LTD BOX 1819 DRAYTON AL 308685 FOOTHILLS ELECTRIC 3932 - 3A ST. NW CALGARY AL 315512 PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB 400002 ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC 406082 PRECAM RENTALS LTD 10116-94TH AVE. FORT ST. JOHN BC
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Here is a 3rd way to compile a COBOL program to access an Oracle database. 'OracleOCI' is a Micro Focus interface that uses neither PRO*COBOL nor ODBC. Here is a summary of the data base access methods illustrated in this document:
4C1. ProCOBOL run separately prior to COBOL compile script mfprocob1
- requires 'NO' DIRECTIVES
4D1. ProCOBOL integrated into COBOL compile (using usual script mfcbl1)
- requires 'preprocess(COBSQL)' directive.
4E1. OCI interface built in to Micro Focus COBOL
- requires 'SQL(targetdb==OracleOCI)' directive
4F1. OpenSQL using ODBC (required for 'targetdb's other than OracleOCI)
- not yet provided for Oracle (ODBC is used for MySQL in Part_2)
Here are lines 22-32 of the COBOL DIRECTIVES file stored in ctl/cobdirectives. See complete file listed on page '4T1'.
# ** Database Directives ** # ---------- uncomment 1 of the following & modify if required ---------- -C SQL(targetdb==ORACLEOCI) # -C "DB2(init==prot db==ar)" # -C SQL(dbman==ODBC) # -C "preprocess(cobsql) cobsqltype==ORACLE8 end-c comp5==yes endp" # # Use "preprocess(cobsql) ... to invoke ORACLE PRO*COBOL automatically # Better than running procobol separately prior to compile # animation then shows EXEC SQL stmts (vs many generated lines) # (NO directive required if calling ProCOBOL separately before compiler) # # OpenESQL targetdb= alternatives to above "preprocess(cobsql) ..." # could be DB2,INFORMIX,MSQLSERVER,SYBASE (not MySQL)
| Note |
|
-C SQL(targetdb==ORACLEOCI) ===========================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login as demo1 --> /home/demo1
================================
#2. vi ctl/cobdirectives <-- #comment out ...(cobsql)..., activate ORACLEOCI
==================== (see listed above)
#3a. mfcbl1 sqlora1a.cbl <-- compile program to load table
=================== - using sqlora1a.cbl (vs sqlora1.cbl above)
#3b. mfcbl1 sqlora2a.cbl <-- compile program to unload table to seqntl file
===================
| Note |
|
#4a. export CUST1IN=dat1/cust1.fix <-- export External-Name INput to sqlora1a
=============================
#4b. export CUST1OUT=tmp/cust1.fix <-- export External-Name OUTput from sqlora2a
=============================
#5. export COBSW="+A" <-- optional for debug (animate)
=================
#6a. rtsora32 cblx/sqlora1a <-- execute program to create/load cust1 table
======================
#6b. rtsora32 cblx/sqlora2a <-- execute program to create/load cust1 table
======================
#7. sqlplus user/pass <-- start SQL*Plus
=================
#8. SQL> select * from cust1 <-- confirm table loaded
=================== - lists all table rows on screen
130140 EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC 139923 JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC 150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC 201120 ALLTYPE RENTAL LTD BOX 1819 DRAYTON AL 308685 FOOTHILLS ELECTRIC 3932 - 3A ST. NW CALGARY AL 315512 PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB 400002 ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC 406082 PRECAM RENTALS LTD 10116-94TH AVE. FORT ST. JOHN BC
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
| 4F1. | ODBC demo for Oracle |
| - not yet provided for Oracle (ODBC is used for MySQL in Part_2) | |
| - ODBC required for 'targetdb's other than ORACLEOCI |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
# mfprocob1 - compile 1 procobol program to .int animation option -a
# - see SQLdemo.doc#Part_4
# - copies source to outdir cblx (required for animation)
# - must be in library superdir with following subdirs:
# (cbls=cobolsource, cpys=copybooks, cblx= output)
# mfprocobx - alternative script to compile to executable option -x
# - only difference is -a changed to -x on cob ... line below
#
# cbls/prgm.cbl ----------> cblx/prgm.cob ---------> cblx/prgm.int
# procob32 compile
pgm="$1";
if [[ ! -f "cbls/$pgm" ]]; then
echo "USAGE: mfprocob1 progname.cbl <--arg1 programname (assumed in cbls)"
echo " ======================"
exit 1; fi
# specify copybook searchpath for MF COBOL
cwd=$(pwd) # save Current Working Directory
export COBCPY=$cwd/cpys:$ORACLE_HOME/precomp/public
# establish COBOL options for Micro Focus COBOL compile
if [[ -f $cwd/ctl/cobdirectives ]]; then
export COBOPT=$cwd/ctl/cobdirectives # directives (-C options)
export EXTFH=$cwd/ctl/extfh.cfg # COBOL File Handler Configuration
else
export COBOPT=$UV/ctl/cobdirectives # directives (-C options)
export EXTFH=$UV/ctl/extfh.cfg # COBOL File Handler Configuration
fi
# convert any UPPER case progname to lower & remove any .ext (.cbl .cbl, etc)
typeset -l ps=$pgm # convert UPPER case progname to lowercase
px=${ps%%.*} # remove any extension (.cbl, etc)
rm -f cblx/$px.* # remove old versions of this program
cp cbls/$pgm cblx # copy source to outdir (for animation)
cd cblx # change to outdir to receive output files
integer psl=$(wc -l < $pgm) # capture line count in program
cat >$px.err <<EOF # init .err file w progname, will append errs
#
#compile: $pgm - $psl lines
EOF
#==========================================
procob mode=ansi iname=$pgm oname=$px.cob
#==========================================
cob -a -P -k $px.cob -o $px >>$px.err 2>&1
#=========================================
coberr=$?
cat $px.err | more
if [[ $coberr -gt 0 ]]; then
echo "#compile: $ps - *FAILED*"
rm -f $px.cbl $px.int $px.idy
else rm -f $px.err; fi
if [ -f $px.o ]; then rm -f $px.o ; fi
cd $cwd # change back up to CWD when compile began
# if .lst was created (-P lst() option), move it to cblst directory
if [ -f cblx/$px.lst ]; then mv -f cblx/$px.lst cblst; fi
# if .lis was created (from PRO*COBOL), move it to cblis directory
if [ -f cblx/$px.lis ]; then mv -f cblx/$px.lis cblis; fi
exit 0
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#!/bin/ksh
# mfcbl1 - Korn shell script from UVSI stored in: /home/uvadm/sf/IBM/
# mfcbl1 - compile 1 program to .int for animation
# - copies source to outdir cblx (required for animation)
# - must be in library superdir with following subdirs:
# (cbls=cobolsource, cpys=copybooks, cblx= output)
# - see MVSCOBOL.doc/VSECOBOL.doc for Operating Instructions
pgm="$1"; # capture progname
if [[ ! -f "cbls/$pgm" ]]; then
echo "USAGE: mfcbl1 progname.cbl <--arg1 programname (assumed in cbls)"
echo " ==================="
exit 1; fi
#
# specify copybook searchpath for MF COBOL
cwd=$(pwd) # save Current Working Directory
export COBCPY=$cwd/cpys # specify copybook search directory
# - may need to add other copybook dirs, example for ORACLE:
# export COBCPY=$cwd/cpys:$ORACLE_HOME/precomp/public
#
# establish COBOL options for Micro Focus COBOL compile
if [[ -f $cwd/ctl/cobdirectives ]]; then
export COBOPT=$cwd/ctl/cobdirectives # directives (-C options)
export EXTFH=$cwd/ctl/extfh.cfg # COBOL File Handler Configuration
else
export COBOPT=$UV/ctl/cobdirectives # directives (-C options)
export EXTFH=$UV/ctl/extfh.cfg # COBOL File Handler Configuration
fi
# convert any UPPER case progname to lower & remove any .ext (.cbl .cbl, etc)
typeset -l ps=$pgm # convert UPPER case progname to lowercase
px=${ps%%.*} # remove any extension (.cbl, etc)
rm -f cblx/$px.* # remove old versions of this program
cp cbls/$pgm cblx # copy source to outdir (for animation)
cd cblx # change to outdir to receive output files
integer psl=$(wc -l < $pgm) # capture line count in program
cat >$px.err <<EOF # init .err file w progname, will append errs
#
#compile: $pgm - $psl lines
EOF
cob -a -P -k $pgm -o $px >>$px.err 2>&1
#======================================
coberr=$?
cat $px.err | more
if [[ $coberr -gt 0 ]]; then
echo "#compile: $ps - *FAILED*"
rm -f $px.cbl $px.int $px.idy
else rm -f $px.err; fi
if [ -f $px.o ]; then rm -f $px.o ; fi
cd $cwd # change back up to CWD when compile began
# if .lst was created (-P lst() option), move it to cblst directory
if [ -f cblx/$px.lst ]; then mv -f cblx/$px.lst cblst; fi
exit 0
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
# cobdirectives - Directives for Micro Focus COBOL Server Express (Unix/Linux) # - for batch compile of COBOL programs migrated from mainframe # - see more at www.uvsoftware.ca/mvscobol.htm#Part_5 # - this file defined in compile scripts mfcbl1 & mfcblA as follows: # # export COBOPT=$RUNLIBS/ctl/cobdirectives # ======================================== -C ANS85 -C CASE -C FILESHARE -C IBMCOMP -C INDD(SYSIN) -C OUTDD(SYSOUT) -C LIST() -C FORM(56) -C PERFORM-TYPE(OSVS) -C SPZERO -C NOSEG -C SIGN(ASCII) -C SOURCEFORMAT(FIXED) -C NOMFCOMMENT # ** Database Directives ** # (uncomment 1 of the following & modify as required) # -C SQL(targetdb==ORACLEOCI) # -C "DB2(init==prot db==ar)" -C SQL(dbman==ODBC) # -C "preprocess(cobsql) cobsqltype==ORACLE8 end-c comp5==yes endp" # # Use "preprocess(cobsql) ... to invoke ORACLE PRO*COBOL automatically # Better than running procobol separately prior to compile # animation then shows EXEC SQL stmts (vs many generated lines) # (NO directive required if calling ProCOBOL separately before compiler) # # OpenESQL targetdb= alternatives to above "preprocess(cobsql) ..." # could be DB2,INFORMIX,MSQLSERVER,SYBASE (not MySQL) # #------------------------------------------------------------------ # ** compile 'options' vs directives ** # Compile 'options' (such -a -P -x etc) cant be spcfd here # but rather in the compile script on the 'cob' command line # -a for animation (generate .int & .idy) # -g for native code (.gnt) # -x compile to executable (not required for batch, use cobrun .int) # -P to create the listing in subdir cblst/progname.lst #------------------------------------------------------------------ # ** 'file configuration' vs COBOL directives ** # File configuration options may be specified in a 'extfh.cfg' file # - see listing at www.uvsoftware.ca/mvscobol.htm#5D1 #------------------------------------------------------------------
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
identification division.
* sqlora1 - demo COBOL-API for Oracle
* - insert customer Name&Address into table from text file
* - first drops (if exists) & recreates the table
* - also see sqlora2 to read table rows write seqntl file
* - see alt versions sqlmyo1 MySQL/ODBC, sqldb21 for DB2
* - see doc at www.uvsoftware.ca/sqldemo.htm#Part_4
* 3 ways to compile: see ctl/cobdirectives at SQLdemo.htm#4T1
* 1. ProCOBOL - called separately, requires no SQL Directives
* 2. preprocess(cobsql) MicroFocus calls ProCobol better animation
* 3. "SQL(targetdb==OracleOCI)" OpenESQL
program-id. sqlora1.
environment division.
input-output section.
file-control.
select cust1 assign external cust1in
organization line sequential access mode sequential.
data division.
file section.
fd cust1 record contains 80 characters.
01 cust1rec.
05 c1num pic 9(6).
05 filler pic x(1).
05 c1name pic x(22).
05 filler pic x(1).
05 c1adrs pic x(22).
05 filler pic x(1).
05 c1city pic x(16).
05 filler pic x(1).
05 c1prov pic x(2).
05 filler pic x(7).
*
working-storage section.
* database options, communications area, & declare section
copy "sqlca.cpy".
exec sql begin declare section end-exec.
*
* database & user/pswd for connect to MySQL, Oracle,& DB2
01 dbname pic x(20).
01 userpass pic x(20).
*
* customer record fields to be inserted into cust1 table
01 csrec.
05 csnum pic 9(6).
05 csname pic x(22).
05 csadrs pic x(22).
05 cscity pic x(16).
05 csprov pic x(2).
exec sql end declare section end-exec.
*
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
procedure division.
begin-program.
**connect to database --> uncomment for MySQL/ODBC,Oracle,or DB2
* - modify cobdirectives, see www.uvsoftware.ca/sqldemo.htm#4T1
* following works for MySQL/ODBC
* move "myodbc351" to dbname.
* move "mysql2/mysql200" to userpass.
* exec sql connect to :dbname user :userpass end-exec.
* following works for DB2
* move "ar" to dbname
* move "db2demo1" to dbuser
* move "db2demo100" to dbpass
* exec sql connect
* TO :dbname USER :dbuser USING :dbpass end-exec.
* following works for Oracle:
move "demo1/demo100" to userpass.
exec sql connect :userpass end-exec.
if sqlcode not = 0 go to sql-error.
*
* drop table & recreate to clear any old table data
exec sql drop table cust1 end-exec.
if sqlcode not = 0 go to sql-error.
exec sql create table cust1
(custno numeric(6) primary key, name char(22),
adrs char(22), city char(16), prov char(2)) end-exec.
if sqlcode not = 0 go to sql-error.
*
* open input file & use loop to get records & insert to table
open input cust1.
mainloop.
read cust1 at end go to cust1eof.
move c1num to csnum, move c1name to csname,
move c1adrs to csadrs, move c1city to cscity,
move c1prov to csprov.
exec sql insert into cust1
(custno, name, adrs, city, prov) values
(:csnum, :csname, :csadrs, :cscity, :csprov) end-exec.
if sqlcode not = 0 go to sql-error.
go to mainloop.
*
* end of file
cust1eof.
display "EOF, cust1 table loaded" upon console.
exec sql commit work release end-exec.
close cust1. stop run.
*
* SQL error rtn - when any SQL error occurs
sql-error.
display "oracle error, SQLCODE = " sqlcode upon console.
display "SQLERRMC = " sqlerrmc upon console.
exec sql rollback work release end-exec.
stop run.
******************* end program sqlora1.cbl *********************
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
identification division.
* sqlora2 - demo cobol-api for oracle
* - read cust1 table & write to a sequential file
* (vs sqlora1 program to create table & load from file)
* - also see sqlora2 to read table rows write seqntl file
* - see alt versions sqlmyo1 mysql/odbc, sqldb21 for db2
* - see doc at www.uvsoftware.ca/sqldemo.htm#part_4
* 4 ways to compile: see ctl/cobdirectives at SQLdemo.htm#4T1
* 1. procobol - called separately, requires no sql directives
* 2. 'preprocess(cobsql) cobsqltype==ORACLE8...' procobol animate
* 3. 'SQL(targetdb==OracleOCI)' OpenESQL interface for Oracle
* 4. 'SQL(dbman==ODBC)' OpenESQL interface MySQL/ODBC
program-id. sqlora2.
environment division.
input-output section.
file-control.
uvM * select cust1 assign external cust1out
select cust1 assign external cust1out
organization line sequential access mode sequential.
data division.
file section.
fd cust1 record contains 80 characters.
01 cust1rec.
05 c1num pic 9(6).
05 filler pic x(1).
05 c1name pic x(22).
05 filler pic x(1).
05 c1adrs pic x(22).
05 filler pic x(1).
05 c1city pic x(16).
05 filler pic x(1).
05 c1prov pic x(2).
05 filler pic x(7).
*
working-storage section.
uvM copy "unixwork1.cpy".
* database options, communications area, & declare section
* copy "sqlca.cpy".
* above copy *cmtd out, mystery how following include works ?
exec sql include "sqlca.cpy" end-exec.
exec sql begin declare section end-exec.
*
* database & user/pswd for connect to mysql, oracle,& db2
01 dbname pic x(20).
01 userpass pic x(20).
*
* customer record fields to be inserted into cust1 table
01 csrec.
05 csnum pic 9(6).
05 csname pic x(22).
05 csadrs pic x(22).
05 cscity pic x(16).
05 csprov pic x(2).
exec sql end declare section end-exec.
*
procedure division.
uvM perform unixproc1.
begin-program.
**connect to database --> uncomment for mysql/odbc,oracle,or db2
* - modify cobdirectives, see www.uvsoftware.ca/sqldemo.htm#4t1
* following works for mysql/odbc
* move "myodbc351" dbname.
* move "mysql2/mysql200" to userpass.
* exec sql connect to :dbname user :userpass end-exec.
* following works for db2
* move "ar" to dbname
* move "db2demo1" to dbuser
* move "db2demo100" to dbpass
* exec sql connect
* to :dbname user :dbuser using :dbpass end-exec.
* following works for oracle:
move "demo1/demo100" to userpass.
exec sql connect :userpass end-exec.
if sqlcode not = 0 go to sql-error.
*
* declare cursor & select cust1 fields for fetch
exec sql declare cust1cursor cursor for select
uvM custno, name1, adrs, city, prov from cust1 end-exec.
if sqlcode not = 0 go to sql-error.
exec sql open cust1cursor end-exec.
if sqlcode not = 0 go to sql-error.
*
* open output file & use loop to fetch rows from table & write
open output cust1.
mainloop.
exec sql fetch cust1cursor into
:csnum, :csname, :csadrs, :cscity, :csprov end-exec.
if sqlcode not = 0 go to table-end.
* move host variables to fd record fields & write record
move csnum to c1num, move csname to c1name,
move csadrs to c1adrs, move cscity to c1city,
move csprov to c1prov.
write cust1rec.
go to mainloop.
*
* end of table
table-end.
display "End of table, all rows written to output file"
upon console.
exec sql commit work release end-exec.
close cust1. stop run.
*
* sql error rtn - when any sql error occurs
sql-error.
display "oracle error detected: " sqlerrmc upon console.
exec sql rollback work release end-exec.
stop run.
******************* end program sqlora2.cbl *********************
uvM copy "unixproc1.cpy".
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
You do not need to care about the intermediate code created by PRO*COBOL, but In case you are interested, here is a sample of the expanded code for compile by Micro Focus server express.
identification division.
* sqlora2 - demo COBOL-API for Oracle
* - read cust1 table & write to a sequential file
* (vs sqlora1 program to create table & load from file)
* - also see sqlora2 to read table rows write seqntl file
* - see alt versions sqlmyo1 MySQL/ODBC, sqldb21 for DB2
* - see doc at www.uvsoftware.ca/sqldemo.htm#Part_4
* 3 ways to compile: see ctl/cobdirectives at SQLdemo.htm#4T1
* 1. ProCOBOL - called separately, requires no SQL Directives
* 2. preprocess(cobsql) MicroFocus calls ProCobol better animation
* 3. "SQL(targetdb==OracleOCI)" OpenESQL
program-id. sqlora2.
environment division.
input-output section.
file-control.
select cust1 assign external cust1out
organization line sequential access mode sequential.
data division.
file section.
fd cust1 record contains 80 characters.
01 cust1rec.
05 c1num pic 9(6).
05 filler pic x(1).
05 c1name pic x(22).
05 filler pic x(1).
05 c1adrs pic x(22).
05 filler pic x(1).
05 c1city pic x(16).
05 filler pic x(1).
05 c1prov pic x(2).
05 filler pic x(7).
*
working-storage section.
* database options, communications area, & declare section
* copy "sqlca.cpy".
* above copy *cmtd out, mystery how following include works ?
01 SQLFPN GLOBAL.
02 SQLFPN-FILE-LEN PIC S9(4) COMP-5 VALUE +11.
02 SQLFPN-FILENAME PIC X(11) VALUE "sqlora2.cbl".
01 SQLCTX GLOBAL PIC S9(9) COMP-5 VALUE +192394944.
01 SQLEXD GLOBAL.
02 SQL-SQLVSN PIC S9(9) COMP-5 VALUE +12.
02 SQL-ARRSIZ PIC S9(9) COMP-5 VALUE +5.
02 SQL-ITERS PIC S9(9) COMP-5.
02 SQL-OFFSET PIC S9(9) COMP-5.
02 SQL-SELERR PIC S9(4) COMP-5.
02 SQL-SQLETY PIC S9(4) COMP-5.
02 SQL-OCCURS PIC S9(9) COMP-5.
02 SQL-CUD PIC S9(9) COMP-5.
02 SQL-SQLEST PIC S9(9) COMP-5.
02 SQL-STMT PIC S9(9) COMP-5.
02 SQL-SQLADTP PIC S9(9) COMP-5 VALUE 0.
02 SQL-SQLTDSP PIC S9(9) COMP-5 VALUE 0.
02 SQL-SQPHSV PIC S9(9) COMP-5.
02 SQL-SQPHSL PIC S9(9) COMP-5.
02 SQL-SQPHSS PIC S9(9) COMP-5.
02 SQL-SQPIND PIC S9(9) COMP-5.
02 SQL-SQPINS PIC S9(9) COMP-5.
02 SQL-SQPARM PIC S9(9) COMP-5.
02 SQL-SQPARC PIC S9(9) COMP-5.
02 SQL-SQPADTO PIC S9(9) COMP-5.
02 SQL-SQPTDSO PIC S9(9) COMP-5.
02 SQL-SQPHR1 PIC S9(9) COMP-5.
02 SQL-SQPHR2 PIC S9(9) COMP-5.
02 SQL-SQPHR3 PIC S9(9) COMP-5.
02 SQL-SQPHR4 PIC S9(9) COMP-5.
02 SQL-SQPHR5 PIC S9(9) COMP-5.
02 SQL-SQFOFF PIC S9(9) COMP-5.
02 SQL-SQCMOD PIC S9(9) COMP-5.
02 SQL-SQFMOD PIC S9(9) COMP-5.
02 SQL-SQHSTV PIC S9(9) COMP-5 OCCURS 5 TIMES.
02 SQL-SQHSTL PIC S9(9) COMP-5 OCCURS 5 TIMES.
02 SQL-SQHSTS PIC S9(9) COMP-5 OCCURS 5 TIMES.
02 SQL-SQINDV PIC S9(9) COMP-5 OCCURS 5 TIMES.
02 SQL-SQINDS PIC S9(9) COMP-5 OCCURS 5 TIMES.
02 SQL-SQHARM PIC S9(9) COMP-5 OCCURS 5 TIMES.
02 SQL-SQHARC PIC S9(9) COMP-5 OCCURS 5 TIMES.
02 SQL-SQADTO PIC S9(4) COMP-5 OCCURS 5 TIMES.
02 SQL-SQTDSO PIC S9(4) COMP-5 OCCURS 5 TIMES.
01 SQ0001 GLOBAL.
02 FILLER PIC X(66) VALUE "select CUSTNO ,NAME ,ADRS ,CIT
- "Y ,PROV from CUST1 ".
01 SQL-RUNTIME-VARS.
02 SQL-IAPXIT-SUCCESS PIC S9(9) COMP-5 VALUE +0.
02 SQL-IAPXIT-FAILURE PIC S9(9) COMP-5 VALUE +1403.
02 SQL-IAPXIT-FATALERR PIC S9(9) COMP-5 VALUE +535.
01 SQLCUD GLOBAL.
02 FILLER PIC S9(4) COMP-5 VALUE +12.
02 FILLER PIC S9(4) COMP-5 VALUE +4128.
* - - - - - 112 lines removed - - - - -
02 FILLER PIC S9(4) COMP-5 VALUE +1.
02 FILLER PIC S9(4) COMP-5 VALUE +0.
* exec sql include sqlca end-exec.
01 SQLCA GLOBAL.
05 SQLCAID PIC X(8).
05 SQLCABC PIC S9(9) COMP-5.
05 SQLCODE PIC S9(9) COMP-5.
05 SQLERRM.
49 SQLERRML PIC S9(4) COMP-5.
49 SQLERRMC PIC X(70).
05 SQLERRP PIC X(8).
05 SQLERRD OCCURS 6 TIMES
05 SQLWARN.
10 SQLWARN0 PIC X(1).
10 SQLWARN1 PIC X(1).
10 SQLWARN2 PIC X(1).
10 SQLWARN3 PIC X(1).
10 SQLWARN4 PIC X(1).
10 SQLWARN5 PIC X(1).
10 SQLWARN6 PIC X(1).
10 SQLWARN7 PIC X(1).
05 SQLEXT PIC X(8).
* exec sql begin declare section end-exec.
*
* database & user/pswd for connect to MySQL, Oracle,& DB2
01 dbname pic x(20).
01 userpass pic x(20).
*
* customer record fields to be inserted into cust1 table
01 csrec.
05 csnum pic 9(6).
05 csname pic x(22).
05 csadrs pic x(22).
05 cscity pic x(16).
05 csprov pic x(2).
* exec sql end declare section end-exec.
*
procedure division.
begin-program.
**connect to database --> uncomment for MySQL/ODBC,Oracle,or DB2
* - modify cobdirectives, see www.uvsoftware.ca/sqldemo.htm#4T1
* following works for MySQL/ODBC
* move "myodbc351" dbname.
* move "mysql2/mysql200" to userpass.
* exec sql connect to :dbname user :userpass end-exec.
*July 24/09 - following does NOT yet work for DB2
* move "ar" to dbname.
* move "db2demo1.db2demo100" to userpass.
* exec sql connect to :dbname user :userpass end-exec.
* following works for Oracle:
move "demo1/demo100" to userpass.
* exec sql connect :userpass end-exec.
MOVE 10 TO SQL-ITERS
MOVE 5 TO SQL-OFFSET
MOVE 0 TO SQL-OCCURS
CALL "SQLADR" USING
SQLCUD
SQL-CUD
CALL "SQLADR" USING
SQLCA
SQL-SQLEST
MOVE 4352 TO SQL-SQLETY
CALL "SQLADR" USING
USERPASS
SQL-SQHSTV(1)
MOVE 20 TO SQL-SQHSTL(1)
MOVE 0 TO SQL-SQHSTS(1)
MOVE 0 TO SQL-SQINDV(1)
MOVE 0 TO SQL-SQINDS(1)
MOVE 0 TO SQL-SQHARM(1)
CALL "SQLADR" USING
SQL-SQHSTV(1)
SQL-SQPHSV
CALL "SQLADR" USING
SQL-SQHSTL(1)
SQL-SQPHSL
CALL "SQLADR" USING
SQL-SQHSTS(1)
SQL-SQPHSS
CALL "SQLADR" USING
SQL-SQINDV(1)
SQL-SQPIND
CALL "SQLADR" USING
SQL-SQINDS(1)
SQL-SQPINS
CALL "SQLADR" USING
SQL-SQHARM(1)
SQL-SQPARM
CALL "SQLADR" USING
SQL-SQHARC(1)
SQL-SQPARC
CALL "SQLBEX" USING
SQLCTX
SQLEXD
SQLFPN .
if sqlcode not = 0 go to sql-error.
*
* declare cursor & select cust1 fields for fetch
* exec sql declare cust1cursor cursor for
* select custno, name, adrs, city, prov from cust1
* end-exec.
if sqlcode not = 0 go to sql-error.
* exec sql open cust1cursor end-exec.
CALL "SQLADR" USING SQ0001 SQL-STMT
MOVE 1 TO SQL-ITERS
MOVE 36 TO SQL-OFFSET
MOVE 0 TO SQL-OCCURS
MOVE 1 TO SQL-SELERR
CALL "SQLADR" USING
SQLCUD
SQL-CUD
CALL "SQLADR" USING
SQLCA
SQL-SQLEST
MOVE 4352 TO SQL-SQLETY
MOVE 0 TO SQL-SQCMOD
CALL "SQLBEX" USING
SQLCTX
SQLEXD
SQLFPN .
if sqlcode not = 0 go to sql-error.
*
* open output file & use loop to fetch rows from table & write
open output cust1.
mainloop.
* exec sql fetch cust1cursor into
* :csnum, :csname, :csadrs, :cscity, :csprov
* end-exec.
MOVE 1 TO SQL-ITERS
MOVE 51 TO SQL-OFFSET
MOVE 0 TO SQL-OCCURS
MOVE 1 TO SQL-SELERR
CALL "SQLADR" USING
SQLCUD
SQL-CUD
CALL "SQLADR" USING
SQLCA
SQL-SQLEST
MOVE 4352 TO SQL-SQLETY
MOVE 0 TO SQL-SQFOFF
MOVE 2 TO SQL-SQFMOD
CALL "SQLADR" USING
CSNUM IN
CSREC
SQL-SQHSTV(1)
MOVE 6 TO SQL-SQHSTL(1)
MOVE 0 TO SQL-SQHSTS(1)
MOVE 0 TO SQL-SQINDV(1)
MOVE 0 TO SQL-SQINDS(1)
MOVE 0 TO SQL-SQHARM(1)
CALL "SQLADR" USING
CSNAME IN
CSREC
SQL-SQHSTV(2)
MOVE 22 TO SQL-SQHSTL(2)
MOVE 0 TO SQL-SQHSTS(2)
MOVE 0 TO SQL-SQINDV(2)
MOVE 0 TO SQL-SQINDS(2)
MOVE 0 TO SQL-SQHARM(2)
CALL "SQLADR" USING
CSADRS IN
CSREC
SQL-SQHSTV(3)
MOVE 22 TO SQL-SQHSTL(3)
MOVE 0 TO SQL-SQHSTS(3)
MOVE 0 TO SQL-SQINDV(3)
MOVE 0 TO SQL-SQINDS(3)
MOVE 0 TO SQL-SQHARM(3)
CALL "SQLADR" USING
CSCITY IN
CSREC
SQL-SQHSTV(4)
MOVE 16 TO SQL-SQHSTL(4)
MOVE 0 TO SQL-SQHSTS(4)
MOVE 0 TO SQL-SQINDV(4)
MOVE 0 TO SQL-SQINDS(4)
MOVE 0 TO SQL-SQHARM(4)
CALL "SQLADR" USING
CSPROV IN
CSREC
SQL-SQHSTV(5)
MOVE 2 TO SQL-SQHSTL(5)
MOVE 0 TO SQL-SQHSTS(5)
MOVE 0 TO SQL-SQINDV(5)
MOVE 0 TO SQL-SQINDS(5)
MOVE 0 TO SQL-SQHARM(5)
CALL "SQLADR" USING
SQL-SQHSTV(1)
SQL-SQPHSV
CALL "SQLADR" USING
SQL-SQHSTL(1)
SQL-SQPHSL
CALL "SQLADR" USING
SQL-SQHSTS(1)
SQL-SQPHSS
CALL "SQLADR" USING
SQL-SQINDV(1)
SQL-SQPIND
CALL "SQLADR" USING
SQL-SQINDS(1)
SQL-SQPINS
CALL "SQLADR" USING
SQL-SQHARM(1)
SQL-SQPARM
CALL "SQLADR" USING
SQL-SQHARC(1)
SQL-SQPARC
CALL "SQLBEX" USING
SQLCTX
SQLEXD
SQLFPN .
if sqlcode not = 0 go to table-end.
* move host variables to FD record fields & write record
move csnum to c1num, move csname to c1name,
move csadrs to c1adrs, move cscity to c1city,
move csprov to c1prov.
write cust1rec.
go to mainloop.
*
* end of table
table-end.
display "End of table, all rows written to output file"
upon console.
* exec sql commit work release end-exec.
MOVE 1 TO SQL-ITERS
MOVE 86 TO SQL-OFFSET
MOVE 0 TO SQL-OCCURS
CALL "SQLADR" USING
SQLCUD
SQL-CUD
CALL "SQLADR" USING
SQLCA
SQL-SQLEST
MOVE 4352 TO SQL-SQLETY
CALL "SQLBEX" USING
SQLCTX
SQLEXD
SQLFPN .
close cust1. stop run.
*
* SQL error rtn - when any SQL error occurs
sql-error.
display "oracle error detected: " SQLERRMC upon console.
* exec sql rollback work release end-exec.
MOVE 1 TO SQL-ITERS
MOVE 101 TO SQL-OFFSET
MOVE 0 TO SQL-OCCURS
CALL "SQLADR" USING
SQLCUD
SQL-CUD
CALL "SQLADR" USING
SQLCA
SQL-SQLEST
MOVE 4352 TO SQL-SQLETY
CALL "SQLBEX" USING
SQLCTX
SQLEXD
SQLFPN .
stop run.
******************* end program sqlora2.cbl *********************
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
identification division.
* sqlora2w - demo COBOL-API for OracleOCI with "whenever"
* (vs sqlcode test after each EXEC SQL as in sqlora1)
* - read cust1 table & write to a sequential file
* (vs sqlora1w program to create table & load from file)
* - also see sqlora2 to read table rows write seqntl file
* - see alt versions sqlmyo1 MySQL/ODBC, sqldb21 for DB2
* - see doc at www.uvsoftware.ca/sqldemo.htm#Part_4
* 3 ways to compile: see ctl/cobdirectives at SQLdemo.htm#4T1
* 1. ProCOBOL - called separately, requires no SQL Directives
* 2. preprocess(cobsql) MicroFocus calls ProCobol better animation
* 3. "SQL(targetdb==OracleOCI)" OpenESQL
* Note - ProCobol preprocess(cobsql) needs "whenever DO perform"
* - OracleOCI (openESQL) compiles ERR unless "DO" removed
program-id. sqlora2.
environment division.
input-output section.
file-control.
select cust1 assign external cust1out
organization line sequential access mode sequential.
data division.
file section.
fd cust1 record contains 80 characters.
01 cust1rec.
05 c1num pic 9(6).
05 filler pic x(1).
05 c1name pic x(22).
05 filler pic x(1).
05 c1adrs pic x(22).
05 filler pic x(1).
05 c1city pic x(16).
05 filler pic x(1).
05 c1prov pic x(2).
05 filler pic x(7).
*
working-storage section.
* database options, communications area, & declare section
* exec oracle option (mode=ansi close_on_commit=yes) end-exec.
exec sql include sqlca end-exec.
exec sql begin declare section end-exec.
*
* setup ODBC Data-Source-Name for connect to MySQL database
01 dbname pic x(20).
01 userpass pic x(20).
*
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
* customer record fields to be inserted into cust1 table
01 csrec.
05 csnum pic 9(6).
05 csname pic x(22).
05 csadrs pic x(22).
05 cscity pic x(16).
05 csprov pic x(2).
exec sql end declare section end-exec.
*
procedure division.
begin-program.
**connect to database --> uncomment for MySQL/ODBC,Oracle,or DB2
* - modify cobdirectives, see www.uvsoftware.ca/sqldemo.htm#4T1
* following works for Oracle:
move "demo1/demo100" to userpass.
exec sql connect :userpass end-exec.
*
* setup 'WHENEVER' SQLERROR (vs 'IF SQLCODE' after each EXEC SQL)
exec sql whenever SQLERROR perform sql-error end-exec.
exec sql whenever NOT FOUND perform table-end end-exec.
* exec sql use ar end-exec.
*
* declare cursor & select cust1 fields for fetch
exec sql declare cust1cursor cursor for
select custno, name, adrs, city, prov from cust1
end-exec.
exec sql open cust1cursor end-exec.
*
* open output file & use loop to fetch rows from table & write
open output cust1.
mainloop.
exec sql fetch cust1cursor into
:csnum, :csname, :csadrs, :cscity, :csprov
end-exec.
if sqlcode not = 0 go to table-end.
exec sql whenever NOT FOUND perform table-end end-exec.
* move host variables to FD record fields & write record
move csnum to c1num, move csname to c1name,
move csadrs to c1adrs, move cscity to c1city,
move csprov to c1prov.
write cust1rec.
go to mainloop.
*
* end of table
table-end.
display "End of table, all rows written to output file"
upon console.
exec sql commit work release end-exec.
close cust1. stop run.
*
* SQL error rtn - whenever any SQL error occurs
sql-error.
display "oracle error detected: " SQLERRMC upon console.
exec sql whenever sqlerror continue end-exec.
exec sql rollback work release end-exec.
stop run.
******************* end program sqlora2w.cbl *********************
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
| 5A1. | preparation to install DB2 |
| - setup directories for download & install | |
| - download DB2 Express-C server .tar.gz archive |
| 5A2. | install DB2 server & documentation |
| 5A3. | configure DB2 server |
| - setup DB2 system users db2inst1, db2fenc1, dasusr1 | |
| - create DB2 instance (in homedir of db2inst1) |
| 5B1. | modify profiles for DB2 users |
| - define DB2DIR in common_profile | |
| - setup PATHs to DB2 executables & libraries in common_profile |
| 5B2. | setup user for DB2 demos (db2demo1) |
| - copy Vancouver Utility env/stub_profile to db2demo1 .bash_profile |
| 5C1. | verify DB2 installation |
| - login db2inst1, generate sample DB, connect,& test select, etc |
| 5D1. | assign priviliges to db2demo1 |
| - grant authority to group apps (db2demo1) to load tables |
| 5E1. | setup subdirs & copy test files for DB2 demo |
| 5E2. | dtree illustration of dbdemo1 subdirs/files used for demos |
| 5F1. | DB2 demo - manual commands to create,load,select |
| - create database ar, connect, load table cust1, select rows |
| 5F2. | batch-file to create & load table cust1 |
| 5G1. | setup to compile COBOL programs for DB2 |
| - modify COBOL directives to 'SQL(dbman==ODBC)' | |
| - compile demo programs sqldb21.cbl & sqldb22.cbl |
| 5H1. | execute COBOL sqldb21.int to load DB2 tables |
| - select to prove table loaded OK | |
| execute COBOL sqldb22.int to unload DB2 tables | |
| - display unloaded file to verify unload |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
| 5I1. | compile ERRORs on sqldb21 & sqldb22 (as of July 2009) |
| DB0103 CSNUM does not have a suitable definition for host variable. | |
| DB0100 SQL0206N "ADRS" is not valid in the context where it is used. | |
| DB0100 SQL0408N value is not compatible with the data type of its | |
| assignment target. Target name is "CUSTNO". SQLSTATE=42821 | |
| - similar programs work OK on MySQL & Oracle |
| 5S1. | test/demo COBOL programs for DB2 |
| 5S1. | sqldb21.cbl - load DB2 table from a text file |
| 5S2. | sqldb22.cbl - unload DB2 table to a text file |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Here are the procedures I used to download & install DB2 9.5 database server. My main intention was to test COBOL access to DB2 tables.
I decided to install in file system /h23 which had 35 gig available (DB2 required only about 4 gig).
#1. Login root
==========
#2. mkdir /h23/db2 <-- make super-directory for DB2
==============
#3. cd /h23/db2 <-- change to DB2 super-dir
===========
#4a. mkdir setup_v95 <-- make subdir for download/setup files
===============
#4b. mkdir v95 <-- make subdir for files created by install
=========
#4c. mkdir v95doc <-- make subdir for documentation
============
https://www.ibm.com/ ===================
#5a. /h23/db2/setup_v95/db2exc_952_LNX_x86_64.tar.gz
===============================================
#5b. /h23/db2/setup_v95/db2_v95_linuxia32_infocenter.tar.gz
======================================================
- I did not use this, I used the pdf documentation (see below)
https://download.boulder.ibm.com/ibmd/pub/ps/products/db2/info/vr95/pdf/en_US/ =============================================================================
#5c. /h23/db2/v95doc/db2ise952.pdf
=============================
- DB2 Version 9.5 for Linux, Unix,& Windows
- Installing DB2 Servers
- I printed this out Duplex (286 pages, 143 sheets)
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#6. cd /h23/db2/setup_v95 <-- change into setup directory
===================== (where .tar archives downloaded)
#7. gunzip db2exc_952_LNX_x86_64.tar.gz
===================================
#8. tar xvf db2exc_952_LNX_x86_64.tar
=================================
- extract files into subdir ./expc/...
#9. cd expc <-- change into subdir with files exracted by tar
=======
#10. ./db2_install -b /h23/db2/v95 <-- execute install script for DB2 server
=============================
#11. cd /h23/db2/setup_v95 <-- change into setup directory
===================== (where .tar archives downloaded)
#12. gunzip db2_v95_linuxia32_infocenter.tar.gz
==========================================
#13. tar xvf db2_v95_linuxia32_infocenter.tar
========================================
- untar files into subdir ./doce/...
#14. cd doce <-- change into subdir with files exracted by tar
=======
#15. ./doce_install -b /h23/db2/v95info <-- execute install script for DB2 info
==================================
| Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
DB2 requires 3 user/group setups:
| db2inst1/db2iadm1 |
|
| db2fenc1/db2fadm1 |
|
| dasusr1/dasadm1 |
|
#1. Login root
==========
#2a. groupadd -g 999 db2iadm1 <-- setup groups required
========================
#2b. groupadd -g 998 db2fadm1
========================
#2c. groupadd -g 997 dasadm1
=======================
#3a. useradd -u 1004 -g db2iadm1 -m -d/h23/db2/db2inst1 -s/bin/bash db2inst1 <-- setup users
=======================================================================
#3b. useradd -u 1003 -g db2fadm1 -m -d/h23/db2/db2fenc1 -s/bin/bash db2fenc1
=======================================================================
#3c. useradd -u 1002 -g dasusr1 -m -d/h23/db2/dasusr1 -s/bin/bash dasusr1
====================================================================
#4a. passwd db2inst1 <-- setup passwords as desired
===============
#4b. passwd db2fenc1
===============
#4c. passwd dasusr1
==============
#5. cd /h23/db2/v95 <-- change into DB2DIR
===============
#6. instance/dascrt -u dasusr1 <-- run script to create DAS
==========================
#7. instance/db2icrt -a SERVER -u db2fenc1 db2inst1 <-- script create Instance
===============================================
- create DB2 instance in homedir of db2inst1 /h23/db2/db2inst1/...
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
To allow any user to access DB2, we will modify the Vancouver Utility common profiles. After installing Vancouver Utilities, the supplied profiles are found in /home/uvadm/env/... To preserve modified profiles, they should be copied to /home/appsadm/env/... documented at https://www.uvsoftware.ca/admjobs.htm#1B1
We will insert/modify only a few lines in the 'common_profile'. See the full listings begining on pages 1C2 of admjobs.htm.
#1. login appsadm --> /home/appsadm
=============
#2. vi env/common_profile
=====================
- insert following to define DB2 server super-directory
and PATHs to executables & libraries
export DB2DIR=/h23/db2/v95
export PATH=$PATH:$DB2DIR/bin:$DB2DIR/adm
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DB2DIR/lib
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login root
==========
#2. adduser -m -g apps db2demo1 <-- setup user for DB2 demos
===========================
#3. passwd db2demo1 <-- set password
===============
#4. cp -p /home/uvadm/env/stub_profile /home/db2demo1/.bash_profile
===============================================================
- suggested profiles at https://www.uvsoftware.ca/admjobs.htm#1C0
- stub_profile calls the common_profile
(PATHs modified above for access to DB2 executables & libraries)
#5. cp -p /home/uvadm/env/stub_profile /h23/db2/db3inst1/.bash_profile
==================================================================
- also copy Vancouver Utilities profile to db2inst1 homedir
- for acess to DB2 executables & Vancouver Utilities
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login db2inst1 --> /h23/db2/db2inst1 (homedir for db2inst1)
==============
#2. db2start <-- start database server
========
#3. db2sampl <-- create sample database
========
#4. db2 connect to sample <-- connect to database
=====================
#5. db2 list tables
===============
Table/View Schema Type Creation time ACT DB2INST1 T 2009-05-30-17.04.29.101291 CUSTOMER DB2INST1 T 2009-05-30-17.04.36.074906 DEPT DB2INST1 A 2009-05-30-17.04.28.086506 EMP DB2INST1 A 2009-05-30-17.04.28.306807 PRODUCT DB2INST1 T 2009-05-30-17.04.35.693445 SALES DB2INST1 T 2009-05-30-17.04.29.318334 STAFF DB2INST1 T 2009-05-30-17.04.29.279386 SUPPLIERS DB2INST1 T 2009-05-30-17.04.36.934416 VSTAFAC2 DB2INST1 V 2009-05-30-17.04.29.703320 46 record(s) selected. (many lines omitted to save space)
#5. db2 select \* from staff where dept = 20
========================================
ID NAME DEPT JOB YEARS SALARY COMM
10 Sanders 20 Mgr 7 98357.50 -
20 Pernal 20 Sales 8 78171.25 612.45
80 James 20 Clerk - 43504.60 128.20
190 Sneider 20 Clerk 8 34252.75 126.50
4 record(s) selected.
#7. db2 connect reset <-- reset database connection
================= - required before db2stop
#8. db2stop <-- stop database server
=======
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login db2inst1
==============
#2. db2 grant DBADM on database to user db2demo1
============================================
- grant authority to db2demo1 to create tables, load tables, etc
#3. db2 update dbm cfg using SYSADM_GROUP apps
==========================================
- would give all users in group 'apps' (db2demo1, etc)
most powerful authority including db2start/db2stop
- but do NOT do this, since it takes authority away from db2inst1/db2iadm1
#3a. db2 update dbm cfg using SYSADM_GROUP db2iadm1
==============================================
- restore super-authority to group 'db2iadm1' (for user db2inst1)
#4. db2stop <-- stop database server
=======
#5. db2start <-- restart server (for authority changes to be effective)
========
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Here are the subdirs/files we will need to test/demo COBOL & DB2. Please see Part_0 to download sqldemofiles.zip, unzip in tmp2/..., and copy desired files into your subdirs setup in /home/mysql2/...
/home/db2demo1 :-----cbls <-- COBOL demo programs : :-----sqldb21.cbl : :-----sqldb22.cbl : :-----testcon1.cbl :-----cblx <-- PRO*COBOL & Micro Focus compiler outputs :-----cblis <-- PRO*COBOL listings :-----cblst <-- Micro Focus COBOL compiler listings :-----cpys <-- copybooks (sqlca.cob) : :-----cust1.cpy : :-----sqlca.cpy : ctl <-- control files : :-----cobdirectives : :-----extfh.cfg :-----dat1 <-- test data files (from UV Software) : :-----cust1.dat : :-----cust1.fix :-----sf <-- shell script Files : :-----db2createloadcust1 :-----tmp <-- temp files (keep working directory clean) : :-----tmp2 <-- temp files for download & unzip : :-----cbls : :-----... as above : :-----sf : :-----sqldemofiles.zip <-- download zip archive of test subdirs/files
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login db2demo1 --> /home/db2demo1
==============
#2. mkdir cbls cblst cblx cpys ctl dat1 tmp tmp2
============================================
- make subdirs to hold demo files
#3a. Download www.uvsoftware.ca/sqldemofiles.zip into tmp2/... #3b. cd tmp2 #3c. unzip sqldemofiles.zip #3d. cd ..
#4a. cp tmp2/cbls/sqldb2* cbls <-- COBOL programs for DB2 demo #4b. cp tmp2/cbls/testcon1.cbl cbls
#5. cp tmp2/cpys/* cpys <-- COBOL copybooks
#6. cp tmp2/ctl/* ctl <-- control files (COBOL directirves)
#7. cp tmp2/dat1/* dat1 <-- test data files to load tables
#8. cp tmp2/sf/db2createloadcust1 sf <-- batch file to create/load cust1 table
If you have Vancouver Utilities installed, you could copy directly from the uvadm/... subdirs to your /home/mysql2/... subdirs:
#4a. cp /home/uvadm/mf/cbls/sqldb2* cbls <-- COBOL programs #4b. cp /home/uvadm/mf/cbls/testcon3.cbl cbls
#5a. cp /home/uvadm/mf/cpys/cust1.cpy <-- COBOL copybooks #5b. cp /home/uvadm/mf/cpys/sqlca.cpy
#6. cp /home/uvadm/ctl/cobdirectives ctl <-- COBOL Directives for compile
#7. cp /home/uvadm/dat1/cust1* dat1 <-- test data files
#8. cp /home/uvadm/sf/demo/sf/db2createloadcust1 sf <-- batch-file create/load
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login db2demo1 <-- login for DB2 demo
============== - see subdirs in homedir listed above
#2. db2 create database ar <-- create DB2 database 'ar'
======================
#3. db2 connect to ar <-- connect to database 'ar'
=================
#4. db2 create table cust1 (custno numeric(6) primary key not null,\
================================================================
name char(22), address char(22), city char(16), prov char(2))
================================================================
- create table 'cust1'
#5. db2 describe table cust1
========================
Data type Column
Column name schema Data type name Length Scale Nulls
CUSTNO SYSIBM DECIMAL 6 0 No
NAME SYSIBM CHARACTER 22 0 Yes
ADRS SYSIBM CHARACTER 22 0 Yes
CITY SYSIBM CHARACTER 16 0 Yes
PROV SYSIBM CHARACTER 2 0 Yes
5 record(s) selected.
#6. db2 load from dat1/cust1.dat of DEL modified by coldel\| insert into cust1
==========================================================================
- load table from '|' pipe delimited file (cust1.dat)
- note 'coldel\|' - no spaces, must \escape '|' on command line
#7. db2 select \* from cust1
========================
- select all rows from cust1 table
- must \escape '*' on unix/linux command line
- would not \escape if you run 'db2' separately, followed by 'select'
CUSTNO NAME ADRS CITY PROV 130140. EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC 139923. JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC 150825. RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC 201120. ALLTYPE RENTAL LTD BOX 1819 DRAYTON AL 308685. FOOTHILLS ELECTRIC 3932 - 3A ST. NW CALGARY AL 315512. PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB 400002. ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC 406082. PRECAM RENTALS LTD 10116-94TH AVE. FORT ST. JOHN BC 8 record(s) selected.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
-- db2createcust1 - batch-file to create cust1 demo table
--
drop table cust1
create table cust1 (custno numeric(6) primary key not null, name char(22),\
address char(22), city char(16), prov char(2))
--
load from dat1/cust1.dat of del modified by coldel| insert into cust1
--
--note: 'coldel|' must be 'coldel\|' on db2 command line, but not in batch-file
#6. db2 <ctl/db2createloadcust1
===========================
#7. db2 select \* from cust1
========================
- select all rows from cust1 table (see output on page above)
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login as db2demo1 --> /home/db2demo1
====================================
#2. vi ctl/cobdirectives
====================
We need to modify the COBOL directives file to specify "DB2(init==prot db==ar)" vs 'SQL(targetdb==ORACLEOCI)' used in Part_4.
Please see the entire COBOL DIRECTIVES file listed on page '4T1'. Here are lines 22-30 after the changes:
# ** Database Directives ** # ---------- uncomment 1 of the following & modify if required ---------- # -C SQL(targetdb==ORACLEOCI) -C "DB2(init==prot db==ar)" # -C SQL(dbman==ODBC) # -C "preprocess(cobsql) cobsqltype==ORACLE8 end-c comp5==yes endp" # # Use "preprocess(cobsql) ... to invoke ORACLE PRO*COBOL automatically # Better than running procobol separately prior to compile # animation then shows EXEC SQL stmts (vs many generated lines) # (NO directive required if calling ProCOBOL separately before compiler) # # OpenESQL targetdb= alternatives to above "preprocess(cobsql) ..." # could be DB2,INFORMIX,MSQLSERVER,SYBASE (not MySQL)
| Note |
|
#3a. mfcbl1 sqldb21.cbl <-- compile program to load table
================== - using script 'mfcbl1'
- see mfcbl1 listed on page '4S2'
#3b. mfcbl1 sqldb22.cbl <-- compile program to unload table to seqntl file
==================
| Note |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login as db2demo1 --> /home/db2demo1
====================================
#2a. export CUST1IN=dat1/cust1.fix <-- export External-Name INput to sqldb21
=============================
#2b. cobrun cblx/sqldb21 <-- execute program to create/load cust1 table
=================== - from dat1/cust1.fix
#3. db2 select \* from cust1 <-- confirm table loaded
======================== - lists all table rows on screen
130140 EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC
139923 JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC
150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC
201120 ALLTYPE RENTAL LTD BOX 1819 DRAYTON AL
308685 FOOTHILLS ELECTRIC 3932 - 3A ST. NW CALGARY AL
315512 PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB
400002 ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC
406082 PRECAM RENTALS LTD 10116-94TH AVE. FORT ST. JOHN BC
#4a. export CUST1OUT=tmp/cust1.fix <-- export External-Name OUTput from sqldb22
=============================
#4b. cobrun cblx/sqldb22 <-- execute program to UNLOAD table to tmp/cust1.fix
====================
#5. cat tmp/cust1.fix
=================
130140 EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC
139923 JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC
150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC
201120 ALLTYPE RENTAL LTD BOX 1819 DRAYTON AL
308685 FOOTHILLS ELECTRIC 3932 - 3A ST. NW CALGARY AL
315512 PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB
400002 ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC
406082 PRECAM RENTALS LTD 10116-94TH AVE. FORT ST. JOHN BC
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
As of July 29/2009, I am getting compile errors on demo programs sqldb21.cbl & sqldb22.cbl. Only testcon3.cbl compiles & runs OK. Here is my 1st attempt (of 3) to compile sqldb21.cbl.
#compile: sqldb21.cbl - 108 lines
* Micro Focus SQL External Compiler Module for IBM DB2 UDB
* Version 4.0.05 (C) copyright 1997-2005 Micro Focus International Ltd.
118 (:csnum, :csname, :csadrs, :cscity, :csprov) end-exec.
* 801-S**************************************************************
** External Compiler Module message
** DB0103 CSNUM does not have a suitable definition for host variable.
| Note |
|
line#21 05 c1num pic x(6). --> pic 9(6). line#44 05 csnum pic x(6). --> pic 9(6). line#75 (custno numeric(6)... --> (custno char(6)...
| Note |
|
#compile: sqldb21.cbl - 108 lines
* Micro Focus SQL External Compiler Module for IBM DB2 UDB
* Version 4.0.05 (C) copyright 1997-2005 Micro Focus International Ltd.
118 (:csnum, :csname, :csadrs, :cscity, :csprov) end-exec.
* 801-S**************************************************************
** External Compiler Module message
** DB0100 SQL0206N "ADRS" is not valid in the context where it is used.
** SQLSTATE=42703
| Note |
|
line#74 exec sql create table cust1
(custno char(6) primary key, name char(22),
city char(16), prov char(2)) end-exec.
*******orig--> adrs char(22), city char(16), prov char(2)) end-exec.
line#88 exec sql insert into cust1
(custno, name, city, prov) values
(:csnum, :csname, :cscity, :csprov) end-exec.
*******orig--> (custno, name, adrs, city, prov) values
*******orig--> (:csnum, :csname, :csadrs, :cscity, :csprov) end-exec.
| Note |
|
#compile: sqldb21x.cbl - 113 lines
* Micro Focus SQL External Compiler Module for IBM DB2 UDB
* Version 4.0.05 (C) copyright 1997-2005 Micro Focus International Ltd.
120 (:csnum, :csname, :cscity, :csprov) end-exec.
* 801-S*****************************************************
** External Compiler Module message
** DB0100 SQL0408N A value is not compatible with the data type of its
** assignment target. Target name is "CUSTNO". SQLSTATE=42821
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
identification division.
* sqldb21 - demo COBOL for DB2, As of July29/09, compile ERRS:
* - "DB0103 CSNUM unsuitable def for host variable"
* - changed 3 lines (then get ADRS not valid in context ?)
* #21 05 c1num pic 9(6) --> pic x(6).
* #44 05 csnum pic 9(6) --> pic x(6).
* #75 create (custno numeric(6) --> char(6) ...
* demo insert customer Name&Address into table from text file
* - first drops (if exists) & recreates the table
* - also see sqldb22 to read table rows write seqntl file
* - see alt versions sqlmyo1 MySQL/ODBC, sqldb21 for DB2
* - see doc at www.uvsoftware.ca/sqldemo.htm#Part_4
* DB2 DIRECTIVE in COBOPT file as follows:
* -C "DB2(init==prot db==ar)" #<-- DB2
* see ctl/cobdirectives listed at SQLdemo.htm#4T1
program-id. sqldb21.
environment division.
input-output section.
file-control.
select cust1 assign external cust1in
organization line sequential access mode sequential.
data division.
file section.
fd cust1 record contains 80 characters.
01 cust1rec.
05 c1num pic 9(6).
05 filler pic x(1).
05 c1name pic x(22).
05 filler pic x(1).
05 c1adrs pic x(22).
05 filler pic x(1).
05 c1city pic x(16).
05 filler pic x(1).
05 c1prov pic x(2).
05 filler pic x(7).
*
working-storage section.
* database options, communications area, & declare section
copy "sqlca.cpy".
exec sql begin declare section end-exec.
* database & user/pswd for connect to MySQL, Oracle,& DB2
01 dbname pic x(20).
01 dbuser pic x(20).
01 dbpass pic x(20).
* customer record fields to be inserted into cust1 table
01 csrec.
05 csnum pic 9(6).
05 csname pic x(22).
05 csadrs pic x(22).
05 cscity pic x(16).
05 csprov pic x(2).
exec sql end declare section end-exec.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
procedure division.
begin-program.
**connect to database --> uncomment for MySQL/ODBC,Oracle,or DB2
* - modify cobdirectives, see www.uvsoftware.ca/sqldemo.htm#4T1
* following works for MySQL/ODBC
* move "myodbc351" to dbname.
* move "mysql2/mysql200" to userpass.
* exec sql connect to :dbname user :userpass end-exec.
* following works for Oracle:
* move "demo1/demo100" to userpass.
* exec sql connect :userpass end-exec.
* if sqlcode not = 0 go to sql-error.
* following works for DB2
move "ar" to dbname
move "db2demo1" to dbuser
move "db2demo100" to dbpass
exec sql connect
TO :dbname USER :dbuser USING :dbpass end-exec.
*
* drop table & recreate to clear any old table data
exec sql drop table cust1 end-exec.
if sqlcode not = 0 go to sql-error.
exec sql create table cust1
(custno numeric(6) primary key, name char(22),
adrs char(22), city char(16), prov char(2)) end-exec.
if sqlcode not = 0 go to sql-error.
*
* open input file & use loop to get records & insert to table
open input cust1.
mainloop.
read cust1 at end go to cust1eof.
move c1num to csnum, move c1name to csname,
move c1adrs to csadrs, move c1city to cscity,
move c1prov to csprov.
exec sql insert into cust1
(custno, name, adrs, city, prov) values
(:csnum, :csname, :csadrs, :cscity, :csprov) end-exec.
if sqlcode not = 0 go to sql-error.
go to mainloop.
*
* end of file
cust1eof.
display "EOF, cust1 table loaded" upon console.
*** exec sql commit work release end-exec.
close cust1. stop run.
*
* SQL error rtn - when any SQL error occurs
sql-error.
display "DB2 error detected: " SQLERRMC upon console.
*** exec sql rollback work release end-exec. stop run.
* Note - committ & rollback ***cmtd out, DB2 NOT supported
******************* end program sqldb21.cbl *********************
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
identification division.
* sqldb22 - demo COBOL-API for DB2
* - read cust1 table & write to a sequential file
* (vs sqldb21 program to create table & load from file)
* - also see sqldb22 to read table rows write seqntl file
* - see alt versions sqlmyo1 MySQL/ODBC, sqldb21 for DB2
* - see doc at www.uvsoftware.ca/sqldemo.htm#Part_4
* DB2 DIRECTIVE in COBOPT file as follows:
* -C "DB2(init==prot db==ar)" #<-- DB2
* see ctl/cobdirectives listed at SQLdemo.htm#4T1
program-id. sqldb22.
environment division.
input-output section.
file-control.
select cust1 assign external cust1out
organization line sequential access mode sequential.
data division.
file section.
fd cust1 record contains 80 characters.
01 cust1rec.
05 c1num pic 9(6).
05 filler pic x(1).
05 c1name pic x(22).
05 filler pic x(1).
05 c1adrs pic x(22).
05 filler pic x(1).
05 c1city pic x(16).
05 filler pic x(1).
05 c1prov pic x(2).
05 filler pic x(7).
*
working-storage section.
* database options, communications area, & declare section
* copy "sqlca.cpy".
* above copy *cmtd out, mystery how following include works ?
exec sql include sqlca end-exec.
exec sql begin declare section end-exec.
*
* database & user/pswd for connect to MySQL, Oracle,& DB2
01 dbname pic x(20).
01 dbuser pic x(20).
01 dbpass pic x(20).
*
* customer record fields to be inserted into cust1 table
01 csrec.
05 csnum pic 9(6).
05 csname pic x(22).
05 csadrs pic x(22).
05 cscity pic x(16).
05 csprov pic x(2).
exec sql end declare section end-exec.
*
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
procedure division.
begin-program.
**connect to database --> uncomment for MySQL/ODBC,Oracle,or DB2
* - modify cobdirectives, see www.uvsoftware.ca/sqldemo.htm#4T1
* following works for MySQL/ODBC
* move "myodbc351" dbname.
* move "mysql2/mysql200" to userpass.
* exec sql connect to :dbname user :userpass end-exec.
* following works for Oracle:
* move "demo1/demo100" to userpass.
* exec sql connect :userpass end-exec.
* if sqlcode not = 0 go to sql-error.
* following works for DB2
move "ar" to dbname
move "db2demo1" to dbuser
move "db2demo100" to dbpass
exec sql connect
TO :dbname USER :dbuser USING :dbpass end-exec.
*
* declare cursor & select cust1 fields for fetch
exec sql declare cust1cursor cursor for select
custno, name, adrs, city, prov from cust1 end-exec.
if sqlcode not = 0 go to sql-error.
exec sql open cust1cursor end-exec.
if sqlcode not = 0 go to sql-error.
*
* open output file & use loop to fetch rows from table & write
open output cust1.
mainloop.
exec sql fetch cust1cursor into
:csnum, :csname, :csadrs, :cscity, :csprov end-exec.
if sqlcode not = 0 go to table-end.
* move host variables to FD record fields & write record
move csnum to c1num, move csname to c1name,
move csadrs to c1adrs, move cscity to c1city,
move csprov to c1prov.
write cust1rec.
go to mainloop.
*
* end of table
table-end.
display "End of table, all rows written to output file"
upon console.
*** exec sql commit work release end-exec.
close cust1. stop run.
* SQL error rtn - when any SQL error occurs
sql-error.
display "DB2 error detected: " SQLERRMC upon console.
*** exec sql rollback work release end-exec. stop run.
* Note - commit & rollback ***cmtd out, DB2 NOT supported
******************* end program sqldb22.cbl *********************
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
| 6A1. | Introduction |
| 6B1. | Install SQL Server on Linux (RedHat Enterprise 7) |
| 6B2. | Install SQL Server Tools |
| 6B3. | odbcinst.ini & odbc.ini listings |
| - exports added to common_profile for ODBC |
| 6B4. | verify ODBC install using odbc_config, odbcinst |
| 6B5. | verify ODBC install using 'isql' |
| - connect to database | |
| - select names of database tables |
| 6C1. | Start SQL Server |
| 6C2. | Capturing outputs from sqlcmd selects. |
| 6D1. | Connect to SQL Server & Test |
| 6D2. | Create simple database/table, populate with a few entries, |
| & query (select with conditions) |
| 6G1. | Setup user (sqluser1) to run the demos |
| 6G2. | Directories in sqluser1 homedir to hold demo files |
| 6H1. | Setup directories in sqluser1 homedir for demo files |
| Download demo files & store in /home/sqluser1/... subdirs | |
| - COBOL programs, copybooks, datafiles, |
| 6I1. | Generating SQL scripts to create & load tables from COBOL copybooks |
| test/demo COBOL copybook for a customer name & address file | |
| cpys/cust1.cpy copybook for d2asc/cust1 80 byte fixed length records | |
| 'uvcopy cobmap1' converts copybook into a 'cobmap' (record layout) |
| 6I2. | 'uvcopy sqlcreate2' generates table create & load scripts from cobmap |
| output scripts listed: | |
| sqlTC1/cust1 to create table & sqlTL1/cust1 to load table |
| 6I3. | Table create & load scripts require pipe delimited variable length input |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
| 6K1. | Convert fixed length datafile to pipe delimited input for 'sqlcreate2' |
| 'uvcopy genpipe1' generates a pipe delimit job from the copybook |
| 6K2. | Execute generated job to convert Fixed-Length data to pipe|delimited data |
| - show sample records before & after conversion to pipe delimited format |
| 6L1. | Execute SQL Server Table Create & Load scripts |
| Verify table loaded successfully - using 'sqlcmd select' |
| 6L2. | Verify table loaded successfully - using 'isql select' |
| 6L3. | Error addressing table without prefixing table name with 'database.schema' |
| It seems the database defaults to 'master' vs 'testdb' spcfd in odbc.ini |
| 6M1. | Prepare to compile COBOL programs for Microsoft SQL Server |
| modify Micro Focus Directives | |
| -C SQL(dbman==ODBC,TARGETDB==MSSQLSERVER) | |
| export COBMODE=64 <-- in common_profile |
| 6M2. | Compile COBOL programs to Load & Unload Tables 'sqlms1.cbl' & 'sqlms2.cbl' |
| - demo programs listed later at '6X1' |
| 6N1. | Execute COBOL programs to Load & Unload Tables |
| - show data unloaded |
| 6N2. | Problem connecting from COBOL |
| 6N3. | Research COBOL connect problem with 'testcon4.cbl' |
| - simple COBOL program with connect only | |
| - still fails, but fewer errors |
| 6X1. | Listings of demo COBOL programs |
| sqlms1.cbl - load a table | |
| 6X2. | sqlms2.cbl - unload a table |
| 6X3. | testcon4.cbl - test connect to SQL Server |
| 6Y1. | Listings of compile scripts & COBOL directives |
| 6Y1. | mfcbl1 - script to compile Micro Focus COBOL programs |
| 6Y2. | cobdirectives - Directives for Micro Focus COBOL compile script |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Microsoft SQL Server for Linux was announced in 2016 (test versions available). General Availability in 2017.
After installing we will test interactively using T-SQL commands to create a simple inventory table with a few values & then 'select * from inventory'.
Then we will show SQL scripts to load & unload a customer name & address table. We can generate these scripts automatically from the COBOL copybook using the Vancouver Utilities, but you could enter them with the editor if you do not have the Vancouver Utilities.
Our goal is prove that SQL Server on Linux works with COBOL programs. We show 2 COBOL programs to load & unload tables & you could extract them from this documentation.
We also show how to convert fixed length records to pipe delimited format for input to the COBOL table load program. We use Vancouver Utilities to to do this automatically based on the COBOL copybook, but again, you could extract the pipe delimited file from this documentation
Hopefully this will help you get started with SQL Server on Linux. In particular, you might appreciate our examples of the 'odbc.ini' file and the environmental variables required in our common_profile.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login root
==========
#2. Download the Microsoft SQL Server Red Hat repository configuration file:
| #2a. | curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo\ |
| > /etc/yum.repos.d/mssql-server.repo | |
| ==================================================================== |
#3. yum install mssql-server <-- Install SQL Server
========================
| Note |
|
#4. /opt/mssql/bin/sqlservr-setup <-- run configuration script
=============================
- follow prompts & setup password for the SA account.
#5. systemctl status mssql-server <-- verify the service is running
=============================
#6. Open the SQL Server port on the firewall on RHEL, to allow remote connections
The default SQL Server port is TCP 1433
#6a. firewall-cmd --zone=public --add-port=1433/tcp --permanent
==========================================================
#6b. firewall-cmd --reloadUpgrade SQL Server
=======================================
#7. Update the mssql-server package on RHEL with any recent changes
#7a. yum update mssql-server
=======================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login root
==========
#2. Download the Microsoft Red Hat repository configuration file.
| #2a. | curl https://packages.microsoft.com/config/rhel/7/prod.repo |
| > /etc/yum.repos.d/msprod.repo | |
| =========================================================== |
#3. Remove any older unixODBC packages, if a previous version
of mssql-tools was installed.
#3a. yum remove unixODBC-utf16 unixODBC-utf16-devel <-- remove old unixODBC pkgs
==============================================
#4. Install mssql-tools with the unixODBC developer package.
#4a. yum install mssql-tools <-- install SQL tools
=======================
#4b. yum install unixODBC-devel <-- install ODBC
==========================
#5. Update to the latest version of mssql-tools
#5a. yum check-update
================
#5b. yum update mssql-tools <-- update to latest version
======================
#6. Add /opt/mssql-tools/bin/ to your PATH environment variable (for bash shell).
#6a. vi ~/.bash_profile <-- edit your user profile (in your homedir)
================== - insert following after your current PATH export's
#6b. export PATH=$PATH:/opt/mssql-tools/bin
======================================
#7. OR (better), update the common profile so all users can use MSSQL
If you have Vancouver Utilities installed, this might be:
#7a. Login appsadm --> /home/apspadm
=============
#7b. vi env/common_profile <-- edit the common profile
===================== - insert following after current PATH exports
#7c. export PATH=$PATH:/opt/mssql-tools/bin
======================================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
The SQL Server install procedures stores odbcinst.ini at /etc/odbcinst.ini Here is the odbcinst.ini file with my 3 #comment lines inserted.
# odbcinst.ini - for SQL Server on Linux, Feb 2017 # /opt/microsoft/msodbcsql/etc/odbcinst.ini <-- installed here # /etc/odbcinst.ini <-- install copies to here # export ODBCSYSINI=/etc/ #<-- default directory for odbcinst.ini [ODBC Driver 13 for SQL Server] Description=Microsoft ODBC Driver 13 for SQL Server Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.4.0 UsageCount=1
I created the following odbc.ini user file, stored at /etc/odbc.ini and added export's in my common_profile as explained on #comments below.
# odbc.ini - setup by Owen Townsend, Feb19/2017 # /etc/odbc.ini <-- this user file location # /etc/odbcinst.ini <-- also see associated system file # export ODBCINI=/etc/odbc.ini <-- in profile for this file # export ODBCSYSINI=/etc/ #<-- Directory with ODBC config (not File odbcinst.ini) # - see test/demos at www.uvsoftware.ca/sqldemo.htm#Part_6 [ODBC Driver 13 for SQL Server] Description=Microsoft ODBC Driver 13 for SQL Server Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.4.0 DATABASE = testdb SERVER = localhost USER = sa PASSWORD = mssqlSA00
The following code was added to the common_profile about line 135. See full listing at https://uvsoftware.ca/admjobs.htm#1C2.
# ** profile addition for SQL Server ** # see www.uvsoftware.ca/sqldemo.htm#Part_6 export PATH=$PATH:/opt/mssql-tools/bin export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/microsoft/msodbcsql/lib64 export ODBCSQL="ODBC Driver 13 for SQL Server" export DATABASE=testdb # $(DATABASE) used in table create & load scripts export ODBCINI=/etc/odbc.ini export ODBCSYSINI=/etc/ #<-- Directory with ODBC config (not File odbcinst.ini) export COBMODE=64 #<-- required for COBOL to match ODBC & SQL Server
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. odbc_config --odbcini <-- show the SYSTEM DSN ini file
=====================
/etc/odbc.ini <-- output
#2. odbc_config --odbcinstini <-- show the DRIVER ini file
=========================
/etc/odbcinst.ini <-- output
#3. odbcinst -q -d <-- list ODBC drivers registered with unixODBC
==============
[ODBC Driver 13 for SQL Server] <-- output
#4. odbcinst -q -s <-- list user ODBC data sources
==============
[ODBC Driver 13 for SQL Server] <-- output
#5. odbcinst -j <-- list ODBC directories
=========== - output listed below
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /etc/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
You can test the ODBC connection to the database using 'isql'. The 'isql' utility is part of 'unixODBC'. It can execute SQL statements similarily to 'sqlcmd' which is part of Microsoft SQL Server.
#1. isql -v 'ODBC Driver 13 for SQL Server' sa mssqlSA00
====================================================
- connect to the database via 'isql'
- displays Connected message & prompts for SQL commands as follows:
+---------------------------------------+
| Connected! |
| sql-statement |
| help [tablename] |
| quit |
+---------------------------------------+
SQL>
#2. SQL> select name from sys.databases <-- display system table names
=================================== - output shown below:
+------------------------------------------------------------------------------+
| name |
+------------------------------------------------------------------------------+
| master |
| tempdb |
| model |
| msdb |
| testdb |
+------------------------------------------------------------------------------+
SQLRowCount returns 0
5 rows fetched
#3. SQL> quit
=========
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login root
#2. systemctl start mssql-server
============================
#3. systemctl enable mssql-server
=============================
#4. Connect to the SQL Server as SysAdmin
#4a. sqlcmd -S localhost -U sa -P mssqlSA00 <-- connect on current machine
======================================
#4b. sqlcmd -U sa -P mssqlSA00 <-- can omit '-S localhost' (assumed)
=========================
#5. Run SQL queries as desired ?
For example, to display names of all databases.
#5a. SELECT Name from sys.Databases; <-- display names of all databases
===============================
#5b. GO
#5c. SELECT Name from sys.Databases; GO <-- cannot combine on same line
===============================
- can't append GO on same line, but we may show this way to save space
name
-------------------------------------------------------------------------
master
tempdb
model
msdb
testdb
(5 rows affected)
#6. QUIT <-- quit/disconnect
====
#1. Login root
#2. systemctl stop mssql-server
===========================
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
It is often desirable to run sqlcmd from a batch script without having to answer any prompts for user/password. The following syntax works to capture output of sqlcmd's for example:
#1. Login root
#2a. sqlcmd -Q "SELECT Name from sys.Databases" -o tmp/dbnames
=========================================================
#2b. cat tmp/dbnames
===============
name
-------------------------------------------------------------------------
master
tempdb
model
msdb
(4 rows affected)
#3a. sqlcmd -U sa -P mssqlSA00 -Q "SELECT table_name from information_schema.tables"
-o tmp/dbtables
===============================================================================
#3b. cat tmp/dbtables
================
table_name
--------------------------------------------------------------------
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_values
spt_monitor
MSreplication_options
(6 rows affected)
#4. quit
====
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login as any user
#2. Connect to SQL Server as the SQL System Administrator 'SA'
- setup at install time
- the only SQL user setup at this point
#2a. sqlcmd -S localhost -U sa -P mssqlSA00 <-- connect on current machine
======================================
#2b. sqlcmd -S 192.555.5.555 -U sa -P mssqlSA00 <-- alternative
==========================================
- connect to SQL Server on some other machine
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Here are some simple T-SQL commands you might run to test SQL Server.
Create a database using the SQL Server default settings.
#1. CREATE DATABASE testdb; GO <-- create database 'testdb'
==========================
| Note |
|
#2. USE testdb; GO <-- use the database
==============
#3. Create a table in the current database:
#3a. CREATE TABLE inventory (id INT, name NVARCHAR(50), quantity INT); GO
====================================================================
#4. Insert data into the new table:
#4a. INSERT INTO inventory VALUES (1, 'banana', 150);
================================================
#4b. INSERT INTO inventory VALUES (2, 'orange', 154);
================================================
#4c. GO
#5. Select from the table:
#5b. SELECT * FROM inventory WHERE quantity > 152; GO
================================================
#6. QUIT <-- quit sqlcmd
====
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login root
==========
#2. adduser -m -g apps sqldemo1 <-- setup user for SQL demos
===========================
#3. passwd sqldemo1 <-- set password
===============
#4. cp -p /home/uvadm/env/stub_profile /home/sqldemo1/.bash_profile
===============================================================
- suggested profiles at https://www.uvsoftware.ca/admjobs.htm#1C0
- stub_profile calls the common_profile
(PATHs modified above for access to SQL executables & libraries)
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Here are the subdirs/files we will need to test/demo COBOL & SQL. Please see Part_0 to download SQLdemofiles.zip, unzip in unzip/..., and copy desired files into your subdirs setup in /home/sqldemo1/...
/home/sqldemo1 :-----cbls <-- COBOL demo programs : :-----sqlms1.cbl : :-----sqlms2.cbl : :-----testcon4.cbl :-----cblx <-- Micro Focus compiler outputs :-----cblst <-- Micro Focus COBOL compiler listings :-----cpys <-- copybooks : :-----cust1.cpy : :-----sqlca.cpy : ctl <-- control files : :-----cobdirectives : :-----extfh.cfg :-----d2asc <-- test data file (Fixed Field format) : :-----cust1 :-----d4pipe <-- test data files (Pipe Delimited format) : :-----cust1 :-----maps <-- copybook record layouts : :-----cust1.map :-----pfp1 <-- uvcopy job convert Fixed field to Pipe Delimited : :-----cust1 :-----sf <-- misc shell Script Files : :----- :-----sqlTC1 <-- Table Create scripts : :-----cust1 :-----sqlTL1 <-- Table Load scripts : :-----cust1 :-----tmp <-- temp files : :-----unzip <-- temp dir to download, unzip,& copy to above : :-----cbls : :-----... as above : :-----tmp
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login sqldemo1 --> /home/sqldemo1
==============
#2. mkdir cbls cblst cblx cpys ctl d2asc tmp unzip
=============================================
- make subdirs to hold demo files
#3a. Download www.uvsoftware.ca/SQLdemofiles.zip into unzip/... #3b. cd unzip #3c. unzip SQLdemofiles.zip #3d. cd ..
#4a. cp unzip/cbls/sqlsql* cbls <-- COBOL programs for SQL demo #4b. cp unzip/cbls/testcon1.cbl cbls
#5. cp unzip/cpys/* cpys <-- COBOL copybooks
#6. cp unzip/ctl/* ctl <-- control files (COBOL directirves)
#7. cp unzip/d2asc/* d2asc <-- test data files to load tables
#8. cp unzip/sf/sqlcreateloadcust1 sf <-- batch file to create/load cust1 table
If you have Vancouver Utilities installed, you could copy directly from the uvadm/... subdirs to your /home/sqldemo1/... subdirs:
#4a. cp /home/uvadm/mf/cbls/sqlsql* cbls <-- COBOL programs #4b. cp /home/uvadm/mf/cbls/testcon3.cbl cbls
#5a. cp /home/uvadm/mf/cpys/cust1.cpy <-- COBOL copybooks #5b. cp /home/uvadm/mf/cpys/sqlca.cpy
#6. cp /home/uvadm/ctl/cobdirectives ctl <-- COBOL Directives for compile
#7. cp /home/uvadm/d2asc/cust1* d2asc <-- test data files
#8. cp /home/uvadm/sf/demo/sf/sqlcreateloadcust1 sf <-- SQL script create/load
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Creating tables manually would be very laborious for files with many fields. UV Software provides uvcopy job 'sqlcreate2' to automatically generate SQL Server scripts to create & load SQL tables from COBOL copybooks.
'sqlcreate2' must be preceded by 'cobmap1' which converts the COBOL copybook to a record-layout (or 'cobmap'). We will demo cobmap1 & sqlcreate2 using COBOL copybook 'cust1.cpy'.
******* cust1.cpy - cobol copy book for cust1 file (for SQL demos)
******* - by Owen Townsend, UV Software, Feb 2017
******* to generate batch file to create table & control file to load
01 cust1record.
10 cm-num pic 9(6).
10 filler pic x(1).
10 cm-name pic x(22).
10 filler pic x.
10 cm-adrs pic x(22).
10 filler pic x.
10 cm-city pic x(16).
10 filler pic x.
10 cm-prov pic x(2).
10 filler pic x(8).
#0. Login sqldemo1 --> /home/sqldemo1/
============== - see subdirs on page '6G2'
#1. uvcopy cobmap1,fili1=cpys/cust1.cpy,filo1=maps/cust1
====================================================
- convert copybook to 'cobmap' (record layout)
cobmap1 COBOL record layout 2017/02/18_15:42:14 pg# 0001 maps/cust1 RCSZ=00080 bgn end lth typ
* cust1.cpy - cobol copy book for cust1 file (for sql demos)
01 cust1record.
10 cm-num pic 9(6). 0000 0005 006 n 6
10 filler001 pic x(1). 0006 0006 001
10 cm-name pic x(22). 0007 0028 022
10 filler002 pic x. 0029 0029 001
10 cm-adrs pic x(22). 0030 0051 022
10 filler003 pic x. 0052 0052 001
10 cm-city pic x(16). 0053 0068 016
10 filler004 pic x. 0069 0069 001
10 cm-prov pic x(2). 0070 0071 002
10 filler005 pic x(8). 0072 0079 008
*RCSZ=00080
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
'sqlcreate2' generates SQL scripts to create & load a table. The input file is a 'cobmap' (record layout generated from the copybook on previous page).
#1. uvcopy cobmap1,fili1=cpys/cust1.cpy,filo1=maps/cust1
====================================================
- convert copybook to a 'cobmap' (record layout)
- this step already performed on previous paage
#2. uvcopy sqlcreate2,fili1=maps/cust1,filo1=sqlTC1/cust1,filo2=sqlTL1/cust1
========================================================================
- convert cobmap to SQL scripts to create & load table
- - - OR use script 'sqlcreateB' (combines both above into 1 short command)
#2a. sqlcreateB cust1 <-- script equivalent of above (shorter command)
================ - runs both cobmap1 & sqlcreate2
/* cust1 - SQL script to CREATE a table on: 20170225:142518 */ /* - generated from COBOL copybook & named the same */ /* - see www.uvsoftware.ca/sqldemo.htm#Part_6 */ /* - GENERATE create & load scripts as follows: */ /* uvcopy sqlcreate2,fili1=maps/fname,filo1=sqlTC1/fname,filo2=sqlTL1/fname */ /*==========================================================================*/ /* - EXECUTE table create script as follows: */ /* sqlcmd -S server -U user -P password -d database -i sqlTC1/cust1 */ /*==========================================================================*/ USE $(DATABASE); /* can put 'export DATABASE=testdb' in profile */ DROP TABLE IF EXISTS cust1; CREATE TABLE cust1 ( cm_num int , --#001 9(6). cm_name varchar (0022) , --#002 x(22). cm_adrs varchar (0022) , --#003 x(22). cm_city varchar (0016) , --#004 x(16). cm_prov char (0002) , --#005 x(2). );
USE $(DATABASE); <-- coded in table create & load scripts ================
export DATABASE=testdb <-- can define before running scripts ====================== - OR define in profile for current project
See code added to common_profile listed on page '6B4' or '6N2'. See complete common_profile listed at https://uvsoftware.ca/admjobs.htm#1C2.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
/* cust1 - SQL script to LOAD a table on: 20170225:142518 */ /* - generated from COBOL copybook & named the same */ /* - see www.uvsoftware.ca/sqldemo.htm#Part_6 */ /* - GENERATE create & load scripts as follows: */ /* uvcopy sqlcreate2,fili1=maps/fname,filo1=sqlTC1/fname,filo2=sqlTL1/fname */ /* ======================================================================== */ /* - EXECUTE table LOAD script as follows: */ /* sqlcmd -S server -U user -P password -d database -i sqlTC1/cust1 */ /* ======================================================================== */ USE $(DATABASE); /* can put 'export DATABASE=testdb' in profile */ TRUNCATE TABLE cust1; BULK INSERT cust1 FROM '/home/sqldemo1/d4pipe/cust1' WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n' )
130140 EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC 139923 JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC 150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC
130140|EVERGREEN MOTORS|1815 BOWEN ROAD|NANAIMO|BC|
139923|JOHNSTONE BOILER|1250 EAST PENDER|VANCOUVER|BC|
150825|RIGGERS INDUSTRIAL|960 - 6TH AVENUE|HOPE|BC|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Before we can load the SQL table, we must convert the data file from fixed-field to the pipe|delimited|format expected by SQL LOADER. We can generate a uvcopy job to do this from the COBOL copybook as follows:
#1. uvcopy cobmap1,fili1=cpys/cust1.cpy,filo1=maps/cust1
====================================================
- convert copybook to 'cobmap' (record layout)
#2. uvcopy genpipe1,fili1=maps/cust1,filo1=pfp1/cust1
=================================================
- convert cobmap to uvcopy job to convert fixed-field to pipe-delimited
- - - OR use script 'genpipeA' (combines above into 1 short command)
#2a. genpipeA cust1 <-- script equivalent of above (shorter command)
==============
# cust1 - uvcopy job to convert EBCDIC/ASCII data to delimited text file
opr='cust1 - uvcopy code generated from copybook: cust1 '
rop=j200000r1 #Run OPtions: increase instrn storage & prompt display output
was=a33000b33000c999000d33000e33000
fili1=?d2asc/cust1,rcs=00080,typ=RSTm10000
filo1=?d4pipe/cust1.dat,rcs=9000,typ=LSTtd3 #<-- DOS option d3 for CR/LF
@run
opn all
loop get fili1,a0
skp> eof
# area a input, see 'get' instrn
# area b translated to ASCII, in case mainframe EBCDIC file (with packed?)
# area c data fields fixed 100 bytes apart in prep for var | delimit instrn
# area d output, see 'put' instrn at end
mvc b0(00080),a0 move input area a to area b
mvc c0(6),b0(6) #1 cm-num
mvc c100(22),b7(22) #2 cm-name
mvc c200(22),b30(22) #3 cm-adrs
mvc c300(16),b53(16) #4 cm-city
mvc c400(2),b70(2) #5 cm-prov
var d0(32000),c0(100),0005,'|'
trt d0(32000),$trtchr
clr c0(000500),' '
put1 put filo1,d0
skp loop
eof cls all
eoj
@pf2=genpipe1.sub
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Before we can load the SQL table, we must convert the data file from fixed-field to the pipe|delimited|format expected by SQL LOADER.
uvcopy pfp1/cust1,fili1=d2asc/cust1,filo1=d4pipe/cust1 ======================================================
130140 EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC 139923 JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC 150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC 201120 ALLTYPE RENTAL LTD BOX 1819 DRAYTON AL 308685 FOOTHILLS ELECTRIC 3932 - 3A ST. NW CALGARY AL 315512 PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB 400002 ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC 406082 PRECAM RENTALS LTD 10116-94TH AVE. FORT ST. JOHN BC
130140|EVERGREEN MOTORS|1815 BOWEN ROAD|NANAIMO|BC|
139923|JOHNSTONE BOILER|1250 EAST PENDER|VANCOUVER|BC|
150825|RIGGERS INDUSTRIAL|960 - 6TH AVENUE|HOPE|BC|
201120|ALLTYPE RENTAL LTD|BOX 1819|DRAYTON|AL|
308685|FOOTHILLS ELECTRIC|3932 - 3A ST. NW|CALGARY|AL|
315512|PARTS PLUS|BOX 510 MAIN ST.|THREE HILLS|AB|
400002|ACKLANDS LTD|945 -2ND AVE.|PRINCE GEORGE|BC|
406082|PRECAM RENTALS LTD|10116-94TH AVE.|FORT ST. JOHN|BC|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login sqldemo1 --> /home/sqldemo1
==============
#2. export DATABASE=testdb <-- define database before running scripts
====================== - OR define in profile for current project
- see 'USE $(DATABASE)' in scripts at '6I2' '6I3'
#3. sqlcmd -U sa -P mssqlSA00 -i sqlTC1/cust1
=========================================
- run sqlcmd with 'SQL scripts' to CREATE the table
#4. sqlcmd -U sa -P mssqlSA00 -i sqlTL1/cust1
=========================================
- run sqlcmd with 'control-file' to LOAD the table
#5. sqlcmd -U sa -P mssqlSA00 -Q "select * from testdb.dbo.cust1"
==============================================================
- display loaded table (8 records)
cm_num cm_name cm_adrs cm_city cm_prov 130140 EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC 139923 JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC 150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC 201120 ALLTYPE RENTAL LTD BOX 1819 DRAYTON AL 308685 FOOTHILLS ELECTRIC 3932 - 3A ST. NW CALGARY AL 315512 PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB 400002 ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC 406082 PRECAM RENTALS LTD 10116-94TH AVE. FORT ST. JOHN BC (8 rows affected)
#5. sqlcmd -U sa -P mssqlSA00
-Q "SELECT table_name from testdb.information_schema.tables"
===================================================================
table_name
-------------------------------------------------------------
inventory
cust1
(2 rows affected)
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#6. isql -v 'ODBC Driver 13 for SQL Server' sa mssqlSA00
====================================================
+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+
SQL> select * from cust1 +------------+-----------------------+-----------------------+-----------------+--------+ | cm_num | cm_name | cm_adrs | cm_city | cm_prov| +------------+-----------------------+-----------------------+-----------------+--------+ | 130140 | EVERGREEN MOTORS | 1815 BOWEN ROAD | NANAIMO | BC | | 139923 | JOHNSTONE BOILER | 1250 EAST PENDER | VANCOUVER | BC | | 150825 | RIGGERS INDUSTRIAL | 960 - 6TH AVENUE | HOPE | BC | | 201120 | ALLTYPE RENTAL LTD | BOX 1819 | DRAYTON | AL | | 308685 | FOOTHILLS ELECTRIC | 3932 - 3A ST. NW | CALGARY | AL | | 315512 | PARTS PLUS | BOX 510 MAIN ST. | THREE HILLS | AB | | 400002 | ACKLANDS LTD | 945 -2ND AVE. | PRINCE GEORGE | BC | | 406082 | PRECAM RENTALS LTD | 10116-94TH AVE. | FORT ST. JOHN | BC | +------------+-----------------------+-----------------------+-----------------+--------+ SQLRowCount returns 0 8 rows fetched SQL> exit
The commands to list the table contents using sqlcmd & isql above were:
#4. sqlcmd -U sa -P mssqlSA00 -Q "select * from testdb.dbo.cust1"
=============================================================
#6. isql -v 'ODBC Driver 13 for SQL Server' sa mssqlSA00
SQL> select * from cust1
=====================================================
| Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
When I first attempted to list the table, using the following command, I got the Error (shown below the command).
#4. sqlcmd -U sa -P mssqlSA00 -Q "select * from cust1"
==================================================
Msg 208, Level 16, State 1, Server uvsoft5, Line 1 Invalid object name 'cust1'.
I then prefixed the table name with 'database.schema' (as shown below) & that worked.
#4. sqlcmd -U sa -P mssqlSA00 -Q "select * from testdb.dbo.cust1"
==============================================================
So it seems the database is defaulting to 'master' & not 'testdb' which is specified in my odbc.ini (see listing on page '6B3'). Anybody know why this is so ?
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login as sqldemo1 --> /home/sqldemo1
====================================
#2. vi ctl/cobdirectives
====================
You may need to modify the COBOL directives file for MS SQL Please see the entire COBOL DIRECTIVES file listed on page '6Y2' Here are lines 28-46 after uncommenting directive for MS SQL
# ** Database Directives ** # (uncomment 1 of the following & modify as required) # -C SQL(dbman==ODBC) -C SQL(dbman==ODBC,TARGETDB==MSSQLSERVER) # --> above works, but following gives error "1507U illegal command line" # -C SQL(dbman==ODBC,TARGETDB==MSSQLSERVER,BEHAVIOUR==ANSI) # -C SQL(dbman==ODBC,TARGETDB==MSSQLSERVER,BEHAVIOUR==MAINFRAME,DIALECT==MAINFRAME,NOCHECK) # # -C SQL(targetdb==ORACLEOCI) # -C "DB2(init==prot db==ar)" # -C "preprocess(cobsql) cobsqltype==ORACLE8 end-c comp5==yes endp" # # Use "preprocess(cobsql) ... to invoke ORACLE PRO*COBOL automatically # Better than running procobol separately prior to compile # animation then shows EXEC SQL stmts (vs many generated lines) # (NO directive required if calling ProCOBOL separately before compiler) # # OpenESQL targetdb= alternatives to above "preprocess(cobsql) ..." # could be DB2,MSSQLSERVER,ORACLEOCI,etc
#3. export COBMODE=64 <-- would only be effective for current login
================= - much better to add to profile
#4. vi $APPSADM/env/common_profile <-- update common_profile
============================== (called by .bash_profile)
export COBMODE=64 <-- inserted about line 115 in my common_profile
================= - listing at https://uvsoftware.ca/admjobs.htm#1C2
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login as sqldemo1 --> /home/sqldemo1
====================================
#2a. mfcbl1 sqlms1.cbl <-- compile program to load table
================== - using script 'mfcbl1'
- see mfcbl1 listed on page '6Y2'
#2b. mfcbl1 sqlms2.cbl <-- compile program to unload table to seqntl file
==================
#2c. mfcbl1 testcon4.cbl <-- compile short program test CONNECT
=================== - written when I had problems (OK now)
| Note |
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#1. Login as sqldemo1 --> /home/sqldemo1
====================================
#2a. export CUST1IN=d2asc/cust1 <-- export External-Name INput to sqlms1
==========================
#2b. cobrun cblx/sqlms1 <-- execute program to create/load cust1 table
=================== - from d2asc/cust1
EOF, cust1 table loaded <-- confirmation message expected
#3. sqlcmd -U sa -P mssqlSA00 -Q "select * from testdb.dbo.cust1"
=============================================================
- confirm table loaded, lists all table rows on screen
cm_num cm_name cm_adrs cm_city cm_prov 130140 EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC 139923 JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC 150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC 201120 ALLTYPE RENTAL LTD BOX 1819 DRAYTON AL 308685 FOOTHILLS ELECTRIC 3932 - 3A ST. NW CALGARY AL 315512 PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB 400002 ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC 406082 PRECAM RENTALS LTD 10116-94TH AVE. FORT ST. JOHN BC (8 rows affected)
#4a. export CUST1OUT=tmp/cust1 <-- export External-Name OUTput from sqlms2
=========================
#4b. cobrun cblx/sqlms2 <-- execute program to UNLOAD table to tmp/cust1
===================
End of table, all rows written to output file <-- confirmation expected
#5. cat tmp/cust1
=============
130140 EVERGREEN MOTORS 1815 BOWEN ROAD NANAIMO BC
139923 JOHNSTONE BOILER 1250 EAST PENDER VANCOUVER BC
150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC
201120 ALLTYPE RENTAL LTD BOX 1819 DRAYTON AL
308685 FOOTHILLS ELECTRIC 3932 - 3A ST. NW CALGARY AL
315512 PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB
400002 ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC
406082 PRECAM RENTALS LTD 10116-94TH AVE. FORT ST. JOHN BC
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
OpenESQL Error: Unable to load ODBC Driver/Driver Manager.
Ensure that your database environment is set correctly.
SQL Server error detected: Connection name not found.
I got the above errors when I 1st attempted to execute the COBOL program. Micro Focus COBOL support (which is great) told me 2 possible reasons:
# odbc.ini - setup by Owen Townsend, Feb19/2017 # /etc/odbc.ini <-- this user file location # /etc/odbcinst.ini <-- also see associated system file # export ODBCINI=/etc/odbc.ini <-- in profile for this file export ODBCSYSINI=/etc/ #<-- Directory with ODBC config (not File odbcinst.ini) # - see test/demos at www.uvsoftware.ca/sqldemo.htm#Part_6 [ODBC Driver 13 for SQL Server] Description=Microsoft ODBC Driver 13 for SQL Server Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.1.0 DATABASE = testdb SERVER = localhost USER = sa PASSWORD = mssqlSA00
The following code was added to the common_profile about line 135. See full listing at https://uvsoftware.ca/admjobs.htm#1C2.
# see www.uvsoftware.ca/sqldemo.htm#Part_6 export PATH=$PATH:/opt/mssql-tools/bin export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/microsoft/msodbcsql/lib64 export ODBCSQL="ODBC Driver 13 for SQL Server" export DATABASE=testdb # $(DATABASE) used in table create & load scripts export ODBCINI=/etc/odbc.ini export ODBCSYSINI=/etc/ #<-- Directory with ODBC config (not File odbcinst.ini) export COBMODE=64 #<-- required for COBOL to match ODBC & SQL Server
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
I wrote a very simple COBOL program to research the COBOL connect problem. See 'testcon4.cbl' listed on page '6X3'. Compile & run as follows:
#1. Login as sqldemo1 --> /home/sqldemo1
====================================
#2. mfcbl1 testcon4.cbl <-- compile program to test connecting only
=================== - see script 'mfcbl1' listed on page '6Y1'
#3. cobrun cblx/testcon4 <-- execute program to 'connect only'
==================== - get following ERRORs as of Feb 2017
OpenESQL Error: Unable to load ODBC Driver/Driver Manager.
Ensure that your database environment is set correctly.
I got the above error when I 1st attempted to execute the COBOL program. Micro Focus COBOL support (which is great) told me I need to run the COBOL compiler in 64 bit mode.
I added 'export COBMODE=64' to the common_profile & the error went away. See profile additions on page '6N2'.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
identification division.
* sqlms1 - demo COBOL-API for SQL Server
* - insert customer Name&Address into table from text file
* - first drops (if exists) & recreates the table
* - also see sqlms2 to read table rows write seqntl file
* - see doc at www.uvsoftware.ca/sqldemo.htm#Part_6
* SQL Server DIRECTIVE in COBOPT file as follows:
* -C SQL(dbman==ODBC,TARGETDB==MSSQLSERVER)
* all ctl/cobdirectives in www.uvsoftware.ca/sqldemo.htm#Part_6
program-id. sqlms1.
environment division.
input-output section.
file-control.
select cust1 assign external cust1in
organization line sequential access mode sequential.
data division.
file section.
fd cust1 record contains 80 characters.
01 cust1rec.
05 c1num pic x(6).
05 filler pic x(1).
05 c1name pic x(22).
05 filler pic x(1).
05 c1adrs pic x(22).
05 filler pic x(1).
05 c1city pic x(16).
05 filler pic x(1).
05 c1prov pic x(2).
05 filler pic x(7).
*
working-storage section.
* database options, communications area, & declare section
copy "sqlca.cpy".
exec sql begin declare section end-exec.
*
* database & user.pswd for connect to MySQL, Oracle,& SQL Server
01 dbname pic x(30).
01 dbuserpass pic x(20).
*
* customer record fields to be inserted into cust1 table
01 csrec.
05 csnum pic x(6).
05 csname pic x(22).
05 csadrs pic x(22).
05 cscity pic x(16).
05 csprov pic x(2).
exec sql end declare section end-exec.
*
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
procedure division.
begin-program.
* connect to SQL Server
move "ODBC Driver 13 for SQL Server" to dbname
move "SA.mssqlSA00" to dbuserpass
exec sql connect TO :dbname AS testdb
USER :dbuserpass end-exec.
if sqlcode not = 0 go to sql-error.
*
* drop table & recreate to clear any old table data
exec sql drop table if exists cust1 end-exec.
if sqlcode not = 0 go to sql-error.
exec sql create table cust1
(cm_num char(6) primary key, cm_name char(22),
cm_adrs char(22), cm_city char(16), cm_prov char(2))
end-exec.
if sqlcode not = 0 go to sql-error.
*
* open input file & use loop to get records & insert to table
open input cust1.
mainloop.
read cust1 at end go to cust1eof.
move c1num to csnum, move c1name to csname,
move c1adrs to csadrs, move c1city to cscity,
move c1prov to csprov.
exec sql insert into cust1
(cm_num, cm_name, cm_adrs, cm_city, cm_prov) values
(:csnum, :csname, :csadrs, :cscity, :csprov) end-exec.
if sqlcode not = 0 go to sql-error.
go to mainloop.
*
* end of file
cust1eof.
display "EOF, cust1 table loaded" upon console.
exec sql commit work release end-exec.
close cust1. stop run.
*
* SQL error rtn - when any SQL error occurs
sql-error.
display "SQL Server error detected: " SQLERRMC upon console.
display "sqlcode=" sqlcode " sqlstate=" sqlstate
" sqlwarn=" sqlwarn " sqlerrmc=" sqlerrmc upon console.
exec sql rollback work release end-exec. stop run.
******************* end program sqlms1.cbl *********************
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
identification division.
* sqlms2 - demo COBOL-API for SQL Server
* - read cust1 table & write to a sequential file
* (vs sqlms1 program to create table & load from file)
* - also see sqlms2 to read table rows write seqntl file
* - see doc at www.uvsoftware.ca/sqldemo.htm#Part_6
* SQL Server DIRECTIVE in COBOPT file as follows:
* -C SQL(dbman==ODBC,TARGETDB==MSSQLSERVER)
* all ctl/cobdirectives in www.uvsoftware.ca/sqldemo.htm#Part_6
program-id. sqlms2.
environment division.
input-output section.
file-control.
select cust1 assign external cust1out
organization line sequential access mode sequential.
data division.
file section.
fd cust1 record contains 80 characters.
01 cust1rec.
05 c1num pic 9(6).
05 filler pic x(1).
05 c1name pic x(22).
05 filler pic x(1).
05 c1adrs pic x(22).
05 filler pic x(1).
05 c1city pic x(16).
05 filler pic x(1).
05 c1prov pic x(2).
05 filler pic x(7).
*
working-storage section.
* database options, communications area, & declare section
* copy "sqlca.cpy".
* above copy *cmtd out, mystery how following include works ?
exec sql include sqlca end-exec.
exec sql begin declare section end-exec.
*
* database & user/pswd for connect to MySQL, Oracle,& SQL Server
01 dbname pic x(30).
01 dbuserpass pic x(20).
*
* customer record fields to be inserted into cust1 table
01 csrec.
05 csnum pic 9(6).
05 csname pic x(22).
05 csadrs pic x(22).
05 cscity pic x(16).
05 csprov pic x(2).
exec sql end declare section end-exec.
*
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
procedure division.
begin-program.
* connect to SQL Server
move "ODBC Driver 13 for SQL Server" to dbname
move "SA.mssqlSA00" to dbuserpass
exec sql connect TO :dbname USER :dbuserpass end-exec.
if sqlcode not = 0 go to sql-error.
*
* declare cursor & select cust1 fields for fetch
exec sql declare cust1cursor cursor for select
cm_num, cm_name, cm_adrs, cm_city, cm_prov from cust1
end-exec.
if sqlcode not = 0 go to sql-error.
exec sql open cust1cursor end-exec.
if sqlcode not = 0 go to sql-error.
*
* open output file & use loop to fetch rows from table & write
open output cust1.
mainloop.
exec sql fetch cust1cursor into
:csnum, :csname, :csadrs, :cscity, :csprov end-exec.
if sqlcode not = 0 go to table-end.
* move host variables to FD record fields & write record
move csnum to c1num, move csname to c1name,
move csadrs to c1adrs, move cscity to c1city,
move csprov to c1prov.
write cust1rec.
go to mainloop.
*
* end of table
table-end.
display "End of table, all rows written to output file"
upon console.
exec sql commit work release end-exec.
close cust1. stop run.
*
* SQL error rtn - when any SQL error occurs
sql-error.
display "SQL Server error detected: " SQLERRMC upon console.
display "sqlcode=" sqlcode " sqlstate=" sqlstate
" sqlwarn=" sqlwarn " sqlerrmc=" sqlerrmc upon console.
exec sql rollback work release end-exec. stop run.
******************* end program sqlms2.cbl *********************
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
* testcon4.cbl - CONNECT test for Microsoft SQL Server on Linux
* - by Owen Townsend, UV Software, February 2017
working-storage section.
copy "sqlca.cpy".
EXEC SQL BEGIN DECLARE SECTION END-EXEC
01 server pic x(32).
01 userpass pic x(32).
EXEC SQL END DECLARE SECTION END-EXEC.
*
procedure division.
move "ODBC Driver 13 for SQL Server" to server
move "SA.mssqlSA00" to userpass
EXEC SQL CONNECT TO :server AS testdb
USER :userpass END-EXEC.
if sqlcode not = 0
display "SQL Server connect test failed" upon console
display "sqlcode=" sqlcode " sqlerrmc=" sqlerrmc
upon console stop run.
*
EXEC SQL DISCONNECT CURRENT END-EXEC.
if sqlcode not = 0
display "Error: cannot disconnect " upon console
display "sqlcode=" sqlcode " sqlerrmc=" sqlerrmc
upon console stop run.
*
display "SQL Server connect test OK" upon console.
stop run.
************** end testcon4.cbl (modified by OT) ***************
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
#!/bin/ksh
# mfcbl1 - Korn shell script from UVSI stored in: /home/uvadm/sf/IBM/
# mfcbl1 - compile 1 program to .int for animation
# - copies source to outdir cblx (required for animation)
# - must be in library superdir with following subdirs:
# (cbls=cobolsource, cpys=copybooks, cblx= output)
# - see MVSCOBOL.doc/VSECOBOL.doc for Operating Instructions
pgm="$1"; cbls="cbls"; cblx="cblx"; # capture progname, default input subdir
test -n "$2" && cbls="$2"; # if arg2 subdir spcfd - use it (vs dflt cbls)
test -n "$3" && cblx="$3"; # if arg2 subdir spcfd - use it (vs dflt cbls)
if [[ ! -f "$cbls/$pgm" ]]; then
echo "USAGE: mfcbl1 progname.cbl [cbls] [cblx]"
echo " ================================="
echo " - arg1 progname mandatory, arg2 default cbls/, arg3 default cblx/"
exit 1; fi
cwd=$(pwd) # capture Current Working Directory
# specify copybook searchpath for MF COBOL
export COBCPY=$cwd/cpys:$cwd/sqls:$COBDIR/cpylib
# establish COBOL options for Micro Focus COBOL compile
export COBOPT=$UV/ctl/cobdirectives # directives (-C options)
export EXTFH=$UV/ctl/extfh.cfg # COBOL File Handler Configuration
# convert any UPPER case progname to lower & remove any .ext (.cbl .cbl, etc)
typeset -l ps=$pgm # convert UPPER case progname to lowercase
px=${ps%%.*} # remove any extension (.cbl, etc)
rm -f $cblx/$px.* # remove old versions of this program
cp $cbls/$pgm $cblx # copy source to outdir (for animation)
cd $cblx # change to outdir to receive output files
integer psl=$(wc -l < $pgm) # capture line count in program
cat >$px.err <<EOF # init .err file w progname, will append errs
#
#compile: $pgm Lines=$psl
EOF
cob -a -P -We -k$pgm -o $px >>$px.err 2>&1
#=========================================
coberr=$?
cat $px.err | head
# pxl1=$(wc -l $px.err); pxl2=${pxl1% *}; pxl3=${pxl2##* };
# if [[ $pxl3 -gt 3 ]]; then
#Jan11/10 - cob -Ws default, need -We to return non-0 for fail test
# - alternate workaround above to test err rpt lines > 3
if [[ $coberr -ne 0 ]]; then
echo "#compile: $ps - *FAILED*"
rm -f $px.cbl $px.int $px.idy
else rm -f $px.err; fi
if [ -f $px.o ]; then rm -f $px.o ; fi
cd $cwd # change back up to CWD when compile began
# if .lst was created (-P lst() option), move it to cblst directory
if [ -f $cblx/$px.lst ]; then mv -f $cblx/$px.lst cblst; fi
exit 0
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
# cobdirectives - Directives for Micro Focus COBOL Server Express (Unix/Linux) # - for batch compile of COBOL programs migrated from mainframe # - see more at www.uvsoftware.ca/mvscobol.htm#Part_5 # - this file defined in compile scripts mfcbl1 & mfcblA as follows: # export COBOPT=$UV/ctl/cobdirectives # =================================== -C ENTCOBOL -C CASE -C IBMCOMP -C INDD(SYSIN) -C OUTDD(SYSOUT) -C LIST() -C FORM(56) -C PERFORM-TYPE(OSVS) -C NOSEG -C SIGN(ASCII) -C SOURCEFORMAT(FIXED) -C NOMFCOMMENT -C SPZERO -C NOCHECKNUM # # ** Database Directives ** # # (uncomment 1 of the following & modify as required) # -C SQL(dbman==ODBC) -C SQL(dbman==ODBC,TARGETDB==MSSQLSERVER) # --> above works, but following gives error "1507U illegal command line" # -C SQL(dbman==ODBC,TARGETDB==MSSQLSERVER,BEHAVIOUR==ANSI) # -C SQL(dbman==ODBC,TARGETDB==MSSQLSERVER,BEHAVIOUR==MAINFRAME,DIALECT==MAINFRAME,NOCHECK) # # -C SQL(targetdb==ORACLEOCI) # -C "DB2(init==prot db==ar)" # -C "preprocess(cobsql) cobsqltype==ORACLE8 end-c comp5==yes endp" # # OpenESQL targetdb= alternatives to above "preprocess(cobsql) ..." # could be DB2,MSSQLSERVER,ORACLEOCI,etc # # Use "preprocess(cobsql) ... to invoke ORACLE PRO*COBOL automatically # Better than running procobol separately prior to compile # animation then shows EXEC SQL stmts (vs many generated lines) # (NO directive required if calling ProCOBOL separately before compiler) # # ** compile 'options' vs directives ** # Compile 'options' (such -a -P -x etc) cant be spcfd here # but rather in the compile script on the 'cob' command line # -a for animation (generate .int & .idy) # -g for native code (.gnt) # -x compile to executable (not required for batch, use cobrun .int) # -P to create the listing in subdir cblst/progname.lst # # ** 'file configuration' vs COBOL directives ** # File configuration options may be specified in a 'extfh.cfg' file # - see listing at www.uvsoftware.ca/mvscobol.htm#5D1
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
| 7A1. | Download C programs & compile script |
| store program in /home/mysql2/src/sqlcust1.c & sqlcust2.c | |
| store script in /home/mysql2/sf/ccmysql |
| 7B1. | Instructions to execute demo program sqlcust1 |
| - sqlcust1 connects to the database, creates the 'cust1' table, | |
| & loads it by INSERTing a row at a time from a "delimited","file" |
| 7B2. | Instructions to execute demo program sqlcust2 |
| - sqlcust2 SELECTs all rows from the 'cust1' table, | |
| converts to "delimited","format", & writes to a text output file. |
| 7C1. | script to compile C programs for MySQL |
| 7D1. | C program to SELECT rows from database table out to a "delimited","file" |
| 7D2. | C program to INSERT rows into a database table from a "delimited","file" |
| Note |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
The demo programs, compile script,& data files are provided on the web site in zip file 'sqldemofiles.zip'.
Please see Part_0 for the instructions to download, unzip,& copy to the subdirs shown below:
/home/mysql2 :-----bin : :-----sqlcust1 <-- executables for demo programs : :-----sqlcust2 :-----dat1 : :-----cust1.txt <-- "delimited","file" to load 'cust1' table : :-----sales1.txt <-- "delimited","file" to load 'sales1' table :-----sf : :-----ccmysql <-- script to compile C programs with MySQL API :-----src : :-----sqlcust1.c <-- C program to INSERT rows from "delimited","file" : :-----sqlcust2.c <-- C program to SELECT rows & write to "delimited"
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Demo program 'sqlcust1.c' (listed at '7D2') will connect to database 'ar', create the 'cust1' table, & load it by INSERTing a row at a time from a "delimited","file".
#1. Login as mysql2 --> /home/mysql2
================================
#2. ccmysql sqlcust1 <-- compile the C MySQL demo program
================ - see ccmysql compile script listed above
- script does not require the .c suffix to be specified
#3. bin/sqlcust1 <-- execute program to create/load cust1 table
============
sqlcust1.c - insert from 'dat1/cust1.txt' into TABLE 'cust1' DB 'ar' 130140,"EVERGREEN MOTORS LTD.","1815 BOWEN ROAD","NANAIMO","BC" 139923,"JOHNSTONE BOILER","1250 EAST PENDER ST.","VANCOUVER","BC" 150825,"RIGGERS INDUSTRIAL","960 - 6TH AVENUE","HOPE","BC" 201120,"ALLTYPE RENTAL LTD.","BOX 1819","DRAYTON VALLEY","AL" 308685,"FOOTHILLS ELECTRIC","3932 - 3A ST. N.W.","CALGARY","AL" 315512,"PARTS PLUS","BOX 510 MAIN ST.","THREE HILLS","AB" 400002,"ACKLANDS LTD","945 -2ND AVE.","PRINCE GEORGE","BC" 406082,"PRECAM RENTALS LTD.","10116-94TH AVE.","FORT ST. JOHN","BC" 8 rows inserted into TABLE 'cust1' DB 'ar' from file 'dat1/cust1.txt'
MySQL stores the database tables in /var/lib/mysql. You do not normally need to know where they are, but in case you are curious:
/var/lib/mysql ============== ./ar/ <-- database 'AR' -rw-rw---- 1 mysql mysql 65 Oct 23 12:09 db.opt <-- options -rw-rw---- 1 mysql mysql 8720 Oct 23 12:11 cust1.frm <-- cust1 formats -rw-rw---- 1 mysql mysql 220 Oct 23 13:32 cust1.MYD <-- cust1 data -rw-rw---- 1 mysql mysql 1024 Oct 23 13:39 cust1.MYI <-- cust1 index -rw-rw---- 1 mysql mysql 8720 Oct 23 14:11 sales1.frm <-- sales1 formats -rw-rw---- 1 mysql mysql 220 Oct 23 15:32 sales1.MYD <-- sales1 data -rw-rw---- 1 mysql mysql 1024 Oct 23 15:39 sales1.MYI <-- sales1 index
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Demo program 'sqlcust2.c' (listed at '7D1') will connect to database 'ar', SELECT all rows from the 'cust1' table, convert to "delimited","format", & write to a text output file.
We assume that you have already created & loaded the 'cust1' table by executing 'sqlcust1' (above), or manually as on pages '1C3'+ or by batch file script 'createcust1b' on page '1D1'.
#1. Login as mysql2 --> /home/mysql2
================================
#2. ccmysql sqlcust2 <-- compile the C MySQL demo program
================ - see ccmysql compile script listed above
- script does not require the .c suffix to be specified
#3. bin/sqlcust2 <-- execute program to create/load cust1 table
============
mysql1.c - print 1st 20 rows of table 'cust1' database 'ar' 130140,"EVERGREEN MOTORS LTD.","1815 BOWEN ROAD","NANAIMO","BC" 139923,"JOHNSTONE BOILER","1250 EAST PENDER ST.","VANCOUVER","BC" 150825,"RIGGERS INDUSTRIAL","960 - 6TH AVENUE","HOPE","BC" 201120,"ALLTYPE RENTAL LTD.","BOX 1819","DRAYTON VALLEY","AL" 308685,"FOOTHILLS ELECTRIC","3932 - 3A ST. N.W.","CALGARY","AL" 315512,"PARTS PLUS","BOX 510 MAIN ST.","THREE HILLS","AB" 400002,"ACKLANDS LTD","945 -2ND AVE.","PRINCE GEORGE","BC" 406082,"PRECAM RENTALS LTD.","10116-94TH AVE.","FORT ST. JOHN","BC" 8 rows printed from table 'cust1' database 'ar'
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
# ccmysql - script to compile mysql client programs
# - by Owen Townsend, UV Software, Oct 23/2008
#
# See https://www.uvsoftware.ca/sqldemo.htm
# ==========================================
# - documents test/demo program sqlcust1.c & sqlcust2.c
#
# ccmysql program # omit the .c
# ===============
# src/program.c <-- source program
# bin/program <-- output
#
sqlinclude="-I/usr/include/mysql"
sqllibs="-L/usr/lib64/mysql -lmysqlclient -lz -lcrypt -lnsl -lm -L/usr/lib64 -lssl -lcrypto"
#
pgm="$1"
if [[ ! -f src/"$pgm".c ]]; then
echo "usage: ccmysql program"
echo " ==============="
echo " - program must be in src/program.c"
echo " - will output to bin/program"
exit 9; fi
#
cc -o bin/$pgm $sqlinclude src/$pgm.c $sqllibs
#=============================================
exit 0
#
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
/* sqlcust1.c - insert lines from delimited file into MySQL table */ /* - MySQL C-API demo program by Owen Townsend, UV Software */
/* See https://www.uvsoftware.ca/sqldemo.htm */ /* ========================================== */ /* - documents how to compile & run the test/demo */ /* - lists script 'ccmysql' to compile this program */ /* - create table 'cust1' in DB 'AR' */ /* - cust1.txt "delimited", demo file provided */ /* - can LOAD DATA INFILE 'cust1.txt' INTO TABLE ar.cust1 */ /* sqlcust1.c (this prgm) - INSERT rows into table from "delimited","file"*/ /* sqlcust2.c (alt prgm) - SELECT rows from table to "delimited","file" */
/*------------------- demo file provided ----------------------- 130140,"EVERGREEN MOTORS LTD.","1815 BOWEN ROAD","NANAIMO","BC" 139923,"JOHNSTONE BOILER","1250 EAST PENDER ST.","VANCOUVER","BC" 150825,"RIGGERS INDUSTRIAL","960 - 6TH AVENUE","HOPE","BC" 201120,"ALLTYPE RENTAL LTD.","BOX 1819","DRAYTON VALLEY","AL" 308685,"FOOTHILLS ELECTRIC","3932 - 3A ST. N.W.","CALGARY","AL" 315512,"PARTS PLUS","BOX 510 MAIN ST.","THREE HILLS","AB" 400002,"ACKLANDS LTD","945 -2ND AVE.","PRINCE GEORGE","BC" 406082,"PRECAM RENTALS LTD.","10116-94TH AVE.","FORT ST. JOHN","BC"
/* Why 'INSERT' delimited data, when we could 'LOAD DATA INFILE' ? */ /* - because this was a test for adding DB access to my 'uvcopy' utility */ /* - uvcopy will allow copy from sequential files to DB table rows & reverse*/ /* - COBOL fixed field records easily loaded into DB table rows */ /* - uvcopy converts fixed field records to delimited records to load table */ /* - see examples at https://www.uvsoftware.ca/sqldemo.htm */
#include <mysql.h> #include <stdio.h> #include <string.h> typedef unsigned int Uint; typedef unsigned long Ulong; typedef unsigned long long Ull; typedef const char cchar;
MYSQL *con2; /* database connector */ MYSQL_RES *res2; /* returned by mysql_use_result (initiate retrieval) */ MYSQL_ROW row2; /* ptr returned by mysql_fetch_row(res2) */ Uint fcnt2; /* number fields in rows returned by mysql_num_fields*/ Ulong *flths2; /* array field lths returned by mysql_fetch_lengths */ MYSQL_FIELD *finfo2; /* array field infos returned by mysql_fetch_fields */
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
char program[20] = "sqlcust1.c"; char DBhost[20] = "localhost"; char DBuser[20] = "mysql2"; char DBpass[20] = "mysql200"; char database[20] = "ar"; char table[20] = "cust1"; char query[256]; /* work area to build stmt for mysql_query() */ cchar *ee; /* ptr to error string returned by mysql_error() */
int ss; /* status test after mysql calls (that return int) */ int rr; /* index to loop thru rows */ int ff; /* index to loop thru fields */
char finame[32]="dat1/cust1.txt"; /* text file delimited input filename*/ FILE *fiptr; /* pointer from fopen */ char firec[256]; /* input record area */ int filth; /* input record length */ int ficnt; /* input record count */ int getrec(char *firec, FILE *fiptr); /* prototype for getrec subfunction */
int main(void) { fprintf(stderr,"%s - insert from '%s' into TABLE '%s' DB '%s'\n" ,program,finame,table,database);
fiptr = fopen(finame,"rb"); /* open input file */
if (!fiptr)
{ fprintf(stderr,"ERR opening input file %s\n",finame);
}
con2 = mysql_init(NULL); /* init database connection structure */
/* Connect to database */ con2 = mysql_real_connect(con2,DBhost,DBuser,DBpass,NULL,0,NULL,0); if (!con2) { fprintf(stderr,"ERR_connect: %s\n",mysql_error(con2)); return(2); }
/* select database (name stored above) */ ss = mysql_select_db(con2,database); if (ss) { fprintf(stderr,"ERR_select_db: %s\n",mysql_error(con2)); return(2); }
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
/* drop table before recreate (to clear any existing data in table) */ sprintf(query,"drop table if exists %s",table); ss = mysql_query(con2,query); if (ss) { fprintf(stderr,"ERR_drop_table %s: %s\n",table,mysql_error(con2)); return(2); }
/* create table */
sprintf(query,"create table %s (custnum numeric(6) primary key\
,custname char(22),custadrs char(22)\
,custcity char(16), custprov char(2))",table);
ss = mysql_query(con2,query); /* submit SQL query */
if (ss)
{ fprintf(stderr,"ERR_create_table %s: %s\n",table,mysql_error(con2));
return(2);
}
/* begin loop to get input delimited text records & insert data into table */
while (1)
{ filth = getrec(firec,fiptr);
if (filth <= 0)
{ break;
}
ficnt++; /* count input records */
fprintf(stderr,firec); /* display input on screen */
/* construct INSERT command (with values from delimited input record) */
sprintf(query,"insert into %s values (%s)",table,firec);
ss = mysql_query(con2,query); /* submit SQL query */
if (ss)
{ fprintf(stderr,"ERR_insert_into %s: %s\n",table,mysql_error(con2));
fprintf(stderr,"%s\n",firec);
return(2);
}
}
fprintf(stderr,"%d rows inserted into TABLE '%s' DB '%s' from file '%s'\n"
,ficnt,table,database,finame);
/* close connection & input file */ mysql_free_result(res2); mysql_close(con2); fclose(fiptr);
return 0; }
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
/*----------------------------- getrec ------------------------------*/ /* get next record from input text file */
int getrec(char *firec, FILE *fiptr) { int ii; char cc;
memset(firec,'\0',256); /* init record area all nulls */
/* begin loop to get each byte until LF or EOF (or error, same as EOF)*/
for (ii=0; ii < 256; ii++)
{ cc = fgetc(fiptr); /* get next character */
if (cc < 0)
{ break; }
firec[ii] = cc; /* store current character */
if (cc == '\n')
{ break; }
}
return(ii); }
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
/* sqlcust2.c - retrieve rows from MySQL table & convert to "delimited", */ /* - MySQL C-API demo by Owen Townsend, UV Software, Nov05/08 */
/* See https://www.uvsoftware.ca/sqldemo.htm */ /* ========================================== */ /* - documents how to compile & run the test/demo */ /* - lists script 'ccmysql' to compile this program */ /* - create table 'cust1' in DB 'ar' */ /* - cust1.txt "delimited", demo file provided */ /* - can LOAD DATA INFILE 'cust1.txt' INTO TABLE ar.cust1 */ /* sqlcust1.c (prior prgm) - INSERT rows into table from "delimited","file"*/ /* sqlcust2.c (this prgm) - SELECT rows from table to "delimited","file" */
/*------------------- demo file provided ----------------------- 130140,"EVERGREEN MOTORS LTD.","1815 BOWEN ROAD","NANAIMO","BC" 139923,"JOHNSTONE BOILER","1250 EAST PENDER ST.","VANCOUVER","BC" 150825,"RIGGERS INDUSTRIAL","960 - 6TH AVENUE","HOPE","BC" 201120,"ALLTYPE RENTAL LTD.","BOX 1819","DRAYTON VALLEY","AL" 308685,"FOOTHILLS ELECTRIC","3932 - 3A ST. N.W.","CALGARY","AL" 315512,"PARTS PLUS","BOX 510 MAIN ST.","THREE HILLS","AB" 400002,"ACKLANDS LTD","945 -2ND AVE.","PRINCE GEORGE","BC" 406082,"PRECAM RENTALS LTD.","10116-94TH AVE.","FORT ST. JOHN","BC"
#include <mysql.h> #include <stdio.h> #include <string.h> typedef unsigned int Uint; typedef unsigned long Ulong; typedef unsigned long long Ull; typedef const char cchar;
char DBhost[20] = "localhost"; char DBuser[20] = "mysql2"; char DBpass[20] = "mysql200"; char database[20] = "ar"; char table[20] = "cust1"; char query[100]; /* work area to build stmt for mysql_query() */
cchar *ee; /* ptr to error string returned by mysql_error() */ int ss; /* status test after mysql calls (that return int) */ int rr; /* index to loop thru rows */ int ff; /* index to loop thru fields */ int ii; /* index to loop thru current field */ int aa; /* switch current field A/N or numeric*/
char foname[32] = "tmp/cust1.txt"; /* filename to write output file */ FILE *foptr; /* pointer from fopen */ char forec[128]; /* concatenate all fields from each row */ int folth; /* output record length */
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
MYSQL *con1; /* database connector */ MYSQL_RES *res1; /* returned by mysql_use_result (initiate retrieval) */ MYSQL_ROW row1; /* ptr returned by mysql_fetch_row(res1) */ Uint fcnt1; /* number fields in rows returned by mysql_num_fields*/ Ulong *flths1; /* array field lths returned by mysql_fetch_lengths */ MYSQL_FIELD *finfo1; /* array field infos returned by mysql_fetch_fields */
int main(void) { fprintf(stderr, "sqlcust2.c - print 1st 20 rows of table '%s' database '%s'\n" ,table,database);
foptr = fopen(foname,"wb"); /* open output file */ if (!foptr) { fprintf(stderr,"ERR opening output file %s\n",foname); }
con1 = mysql_init(NULL); /* init database connection structure */
/* Connect to database */ con1 = mysql_real_connect(con1,DBhost,DBuser,DBpass,NULL,0,NULL,0); if (!con1) { fprintf(stderr,"ERR_connect: %s\n",mysql_error(con1)); return(1); }
/* select database (name stored above) */ ss = mysql_select_db(con1,database); if (ss) { fprintf(stderr,"ERR_select_db: %s\n",mysql_error(con1)); return(1); }
/* select all columns & all rows from table */
sprintf(query,"select * from %s",table); /* build SQL statement */
ss = mysql_query(con1,query); /* submit SQL query */
if (ss)
{ fprintf(stderr,"ERR_select_from_table: %s\n",mysql_error(con1));
return(1);
}
/* initiate retrieval from result set */ res1 = mysql_use_result(con1); if (!res1) { fprintf(stderr,"ERR_use_result: %s\n",mysql_error(con1)); return(1); }
/* fetch field count & field info structures for all fields in result set */ fcnt1 = mysql_num_fields(res1); /* get no of fields in rows */ finfo1 = mysql_fetch_fields(res1); /* get array of FIELD info structures*/
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
/* begin loop to get each row & print all fields in each row */
/* - will insert '|' pipe delimiters between fields */
for (rr=0; rr < 20; rr++)
{ row1 = mysql_fetch_row(res1);
if (!row1)
{ ee = mysql_error(con1);
if (!ee)
{ fprintf(stderr,"ERR on fetch_row: %s\n",ee);
return(1);
}
break;
}
/* process fields in current row */
/* 1st get lengths of all fields in current row */
flths1 = mysql_fetch_lengths(res1);
memset(forec,'\0',128); /* init out area to all nulls */
/* begin loop to "delimit","fields" in current row & concatenate to output*/
for (ff=0; ff < fcnt1; ff++)
{ /* determine if current field numeric or A/N */
aa = 0; /* presume numeric */
/* scan data for any non-numeric except '-', ',', '.' */
/* - allow '-' only in 1st byte of data */
for (ii = 0; row1[ff][ii]; ii++)
{ if ((row1[ff][ii] == '-') && (ii == 0))
{ continue;
}
if ((row1[ff][ii] == ',') || (row1[ff][ii] == '.'))
{ continue;
}
if (!(isdigit(row1[ff][ii])))
{ aa = 1;
break;
}
}
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
/* ready to begin copying data for current sub-field */
/* insert delimiter at begin field, depending on: */
/* if 1st field A/N insert '"', if numeric insert nothing*/
/* if 2nd+ field A/N insert ',"', if numeric insert ',' */
if (ff == 0)
{ if (aa)
{ strcat(forec,"\"");
}
}
else
{ if (aa)
{ strcat(forec,",\"");
}
else
{ strcat(forec,"\"");
}
}
/* now copy data to output */
strcat(forec,row1[ff]);
/* append ending delimiter if A/N, else nothing */
if (aa)
{ strcat(forec,"\"");
}
}
strcat(forec,"\n"); /* append LineFeed at end each row */
fprintf(stderr,forec); /* display on screen */
folth = strlen(forec); /* get string length to write */
fwrite(forec,1,folth,foptr); /* write to a file for later printing */
}
fprintf(stderr,"%d rows printed from table '%s' database '%s'\n"
,rr,table,database);
/* close connection & output file */ mysql_free_result(res1); mysql_close(con1); fclose(foptr);
return 0; }
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
| 8A1. | 'uycopy' - new version of 'uvcopy' to provide DataBase access |
| - uvcopy/uycopy differences & uycopy requirements | |
| - typ=DBT specifies a DataBase Table |
| 8A2. | Environmental-Variables for Host, User,& Password (can put in profile) |
| - export DBhost1=... DBuser1=... DBpass1=... |
| 8A3. | 'uycopy rules' for database/tables vs directory/files |
Here are some 'uycopy' sample jobs to load/unload SQL DataBases from/to "delimited","files". This is an interpretive utility, much easier than writing C programs.
| 8B1. | loadsales1 - load sales1.txt "delimited","file" to the 'sales1' table |
| - see sales1 table created & loaded manually on page '1C3' |
| 8B2. | unloadsales1 - unload 'sales1' table to "delimited","file" 'sales1.txt' |
| - see listing on page '1C1' |
| 8B3. | modifysales1 - modify table by copy out to a file or another table |
| & perform changes as we reload the table, for example: | |
| - increase prices by 10% & recalculate extended amount |
| 8B4. | loadsalesE - similar to loadsales1, but input is original EBCDIC file |
| - converts Fixed-Field to "delimited", in same job as load | |
| - saves having to delimit in separate job, see page '9A7' | |
| - also translates EBCDIC to ASCII, inserts decimal points, | |
| inserts leading separate signs, converts date formats, etc |
| Note |
#2. Login as uvadm --> /home/uvadm
#3. ccmysqlLNX64 uycopy <-- execute script to compile 'uycopy'
===================
See script 'ccmysqlLNX64' listed at https://www.uvsoftware.ca/install.htm#F1
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
'uvcopy' is a powerful data manipulation utility from UV Software. It is especially valuable for handling data files migrated from mainframes because it can handle mainframe features not handled by any unix/linux system utility:
In November 2008, 'uycopy' was created to provide access to SQL DataBase tables. 'uycopy' had to be a separate version (from uvcopy) because it requires the SQL C-API libraries for compilation at sites with an SQL DataBase installed.
'uycopy' adds file 'typ=DBT' to define a DataBase Table & 3 environmental variables (DBhost1, DBuser1, DBpass1) for connection to the DataBase. Please inspect the loadsales1/unloadsales1 sample jobs listed on the next 2 pages and note the 'fili1/filo1' (Input/Output) declarations:
fili1=?dat1/sales1.txt,typ=LSTt,rcs=80 <-- Directory/File dat1/sales1.txt #=================-=================== - uvcopy provides only Directory/File
filo1=?ar/sales1,typ=DBT,rcs=2048 <--declare Output DataBase/Table as ar/sales1 #================================ - only uycopy provides typ=DBT database/table
fili1=?ar/sales1,typ=DBT,rcs=2048 <-- declare Input DataBase/Table as ar/sales1 #================================
For Input typ=DBT, a 'get' will convert the database/table 'ROW' to "delimited","field","format" as shown below.
get fili1,a0 <-- get next 'ROW' from DATABASE/TABLE ar/sales1
=============== - converts to "delimited","format" in area 'a'
- from 1st byte 'a0' (displacements 0 relative)
- 1st record for our sample data would be:
130140,21,2004-08-02,"INV11201","HAM001",000010,00012.00,0000120.00 ===================================================================
For Output typ=DBT, a 'put' will INSERT "delimited","field","format" into the current database/table 'ROW'.
put filo1,c0 <-- write a 'ROW' in DATABASE/TABLE ar/sales1
=============== - from area 'c' "delimited","format"
Can do any other processing required while loading/unloading database tables. For example, see 'modifysales1' (page '8B3') to increase prices by 10%
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
export DBhost1=localhost <-- host defaults to 'localhost' if not exported ========================
export DBuser1=userid <-- userid defaults to login user if not exported =====================
export DBpass1=password <-- will prompt for password if not exported =======================
Environmental Variables for host,user,passwd can be in the profile. The profiles supplied by UV Software (see ADMjobs.htm#Part_1) will define these as DBhost1=localhost, DBuser1=mysql2,& DBpass1=mysql200.
There is a similar set of variables (DBhost2,DBuser2,DBpass2) that if undefined will default to the values for DBhost1,DBuser1,DBpass1. These are used for the output database/table (vs the input database/table) in case you wish to copy database tables between different databases & different machines.
Do NOT confuse the 'uycopy' jobs here in Part 8 with the 'uvcopy' jobs in Part_9. The 'uycopy' jobs access the Database/Tables directly via fili/filo 'typ=DBT' (only in uycopy, new as of Nov 2008).
The uvcopy jobs in Part_9 were used prior to uycopy availability & are still useful unconnected from the database. They convert Fixed-Field to "delimited" & vice-versa, prior/after loading/unloading files to/from database tables using SQL commands manually or in scripts.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
# loadsales1 - load a DataBase Table from a "delimited","field","record"
# - by Owen Townsend, UV Software, November 2008
# - see www.uvsoftware.ca/sqldemo.htm
#
# Environmental Variables for host,user,passwd can be in profile
# export DBhost2=localhost <-- host defaults to 'localhost' if not exported
# export DBuser2=userid <-- userid defaults to login user if not exported
# export DBpass2=password <-- will prompt for password if not exported
# DBhost2,DBuser2,DBpass2 for filo1/output (DBhost1,DBuser1,DBpass1 for input)
#
# uycopy loadsales1 <-- execute this job (files default as shown below)
# ================= - sample I/O for 1st record in file below:
#
# 130140,21,2004-08-02,"INV11201","HAM001",000010,00012.00,0000120.00 <-- Input
# cust#,slsmn,invdate ,invoice# ,product ,qty ,price ,ext-amount
#
# Original input was a Fixed-Field record layout from a COBOL application
# - input was converted to "delimited","format" by uycopy job 'delimsales1'
# - could combine the delimit & load into 1 job (see loadsales2)
#
# Output table must have been previously created & all fields defined
# corresponding to the delimited file used to load the table.
#
fili1=?dat1/sales1.txt,typ=LST,rcs=80 #<-- subdir/file ("delimited","text")
filo1=?ar/sales1,typ=DBT,rcs=2048 #<-- database/table output
#================================
# 'typ=LST' - identifies 'dat1/sales1.txt' as Line Sequential Terminated file
# - a text file defined by path DIRECTORY/FILE
# 'typ=DBT' - identifies 'ar/sales1' as a DATABASE/TABLE ROWs
@run
opn all
#
# begin loop to process each record - until EOF
loop get fili1,a0 get next record ("delimited","format")
skp> eof (cc set > at EOF)
# --- ------- <-- could convert to delimited here
# (saving a separate preceding delimit job)
put filo1,a0 write a 'ROW' in DATABASE/TABLE ar/sales1
skp loop repeat loop
#
# EOF - close files & end job
eof cls all
eoj
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
# unloadsales1 - unload a DataBase Table to a "delimited","record"
# - by Owen Townsend, UV Software, November 2008
# - see www.uvsoftware.ca/sqldemo.htm
# - this job is the opposite of 'loadsales1'
#
# Environmental Variables for host,user,passwd can be in profile
# export DBhost1=localhost <-- host defaults to 'localhost' if not exported
# export DBuser1=userid <-- userid defaults to login user if not exported
# export DBpass1=password <-- will prompt for password if not exported
# DBhost1,DBuser1,DBpass1 for fili1/input (DBhost2,DBuser2,DBpass2 for output)
#
# uycopy unloadsales1 <-- execute this job (files default as shown below)
# =================== - sample I/O for 1st record in file below:
#
# 130140,21,2004-08-02,"INV11201","HAM001",000010,00012.00,0000120.00 <-- Output
# cust#,slsmn,invdate ,invoice# ,product ,qty ,price ,ext-amount
#
fili1=?ar/sales1,typ=DBT,rcs=2048 # database/table
filo1=?dat1/sales1.txt,typ=LSTt,rcs=60 # subdir/file
#=====================================
# ar/sales1,typ='DBT' is DATABASE/TABLE
# dat1/sales1.txt,typ='LSTt' DIRECTORY/FILE (Line Seqntl Terminated truncated)
@run
opn all
#
# begin loop to process each record - until EOF
loop get fili1,a0 get next 'ROW' from DATABASE/TABLE ar/sales1
skp> eof (cc set > at EOF)
# --- ------- <-- could undelimit here (or whatever desired)
put filo1,a0 write "delimited","text" record
skp loop repeat loop
#
# EOF - close files & end job
eof cls all
eoj
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
# modifysales1 - copy table to alternate & copy/modify back to original
# --> increase prices by 10%
# - by Owen Townsend, UV Software, November 2008
# - see www.uvsoftware.ca/sqldemo.htm
#
# Plan: DROP any old table1a, CREATE table1save from table1, and
# run this job to copy table1a back to table1 increasing prices 10%
#
# 1. mysql -pmysql200
# 2. mysql> drop table if exists ar.sales1a;
# 3. mysql> create table ar.sales1a select * from ar.sales1;
# 4. quit;
# 5. uycopy modifysales1 <-- execute this demo job
# ===================
#Note - to save running mysql client, you could run following from linux prompt:
# 2. echo "drop table if exists ar.sales1a" | mysql -pmysql200
# 3. echo "create table ar.sales1a select * from ar.sales1" | mysql -pmysql200
#
# cust#,slm,invdate ,invoice# ,product ,qty ,price ,ext-amount
# 130140,21,2004-08-02,"INV11201","HAM001",000010,00012.00,0000120.00 <--1st rec
# 130140 21 2004-08-02 INV11201 HAM001 000010 00012.00 0000120.00 <--Fixed
# b0(6) b100(2) b200(10) b300(8) b400(6) b500(6) b600(8) b700(10) <-- adrses
#
fili1=?ar/sales1a,typ=DBT,rcs=2048 #<-- copy from sales1a (backup of sales1)
filo1=?ar/sales1,typ=DBT,rcs=2048 #<-- copy back to sales1 increasing prices
#================================
@run
opn all
# begin loop to process each record - until EOF
loop get fili1,a0 get next record "delimited","format"
skp> eof (cc set > at EOF)
#
# convert "delimited", to Fixed-Field, so we can access fields easily
und b0(100),a0(80),8 cnvts 8*100 byte fields (data left adjusted)
#
# See Fixed-Field-Addresses above with 1st line of data illustration
# Increase price by 10% & recalculate extended amount
mvn $ca1,b600(8) capture price in work counter
div $ca1,10 calc 10%
add $ca1,b600(8) add orig = new price 10% more
edt b600(8),$ca1,'99999.99' restore price back to fixed-fields
mpy $ca1,b500(6) recalc extended amount
edt b700(10),$ca1,'-999999.99' move new amount to fixed-fields
#
# convert back to "delimited", for table load
dlmn1 c0(80),b0(100),8 convert 8*100 byte fields to delimited
put filo1,c0 write a row in DATABASE/TABLE ar/sales1
skp loop repeat loop
#
# EOF - close files & end job
eof cls all
eoj
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
# loadsalesE - load a DataBase Table from a mainframe EBCDIC fixed field file
# - by Owen Townsend, UV Software, November 2008
# - see www.uvsoftware.ca/sqldemo.htm
#
# Environmental Variables for host,user,passwd can be in profile
# export DBhost2=localhost <-- host defaults to 'localhost' if not exported
# export DBuser2=userid <-- userid defaults to login user if not exported
# export DBpass2=password <-- will prompt for password if not exported
# DBhost2,DBuser2,DBpass2 for filo1/output (DBhost1,DBuser1,DBpass1 for input)
#
# uycopy loadsalesE <-- execute this job (files default as shown below)
# ================= - sample I/O for 1st record in file below:
# (Input shown in ASCII since could not read Ebcdic)
#
# cust# 01-06, salesman 08-09, date 11-18, inv# 20-27, product 29-34 <-- layout
# quantity 36-41, unit price 43-49, extended amount 51-59
#
# 130140 21 20040802 INV11201 HAM001 000010 0001200 000012000 <-- Input
# 130140,21,2004-08-02,"INV1120","HAM001",000010,00012.00,0000120.00 <-- Delimtd
#
# Converts fixed field sales records to "delimited","fields" & load DB table
# - in 1 operation vs separate job delimcust1 + SQL LOAD
# uycopy can perform any field processing required:
# - translate EBCDIC to ASCII, insert decimal points & separate signs,
# convert date formats to SQL standards, etc
#
fili1=?dat1/sales1_origE,typ=RSF,rcs=60 #<-- "delimited","text","file" input
filo1=?ar/sales1,typ=DBT,rcs=2048 #<-- database/table output
#================================
# 'typ=RSF' - identifies 'dat1/sales1.origE' as Record Sequential File
# - Fixed-Field file defined by DIRECTORY/FILE path
# 'typ=DBT' - identifies 'ar/sales1' as a DATABASE/TABLE ROWs
@run
opn all
#
# begin loop to process each record - until EOF
loop get fili1,a0 get next record (fixed 60 bytes)
skp> eof (cc set > at EOF)
tra a0(64) translate EBCDIC to ASCII
mvc b0(6),a0 cust#
mvc b100(2),a7 salesman
edt b200(10),a10(8),'9999-99-99' date (insert '-'s for SQL)
mvc b300(8),a19 invoice#
mvc b400(6),a28 product code
mvc b500(-6),a35(6ze) qty (cnvt Zoned EBCDIC signs to leading)
edt b600(8),a42(7),'99999.99' price (insert decimal point)
edt b700(10),a50(9ze),'-999999.99' amount (leading sign & dcml pt)
dlmn1 c0(100),b0(100),8 delimit 8*100 fields from area b to c
put filo1,c0 write a 'ROW' in DATABASE/TABLE ar/sales1
skp loop repeat loop
#
# EOF - close files & end job
eof cls all
eoj
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
| 9A1. | Test files for MySQL demos ('cust1' & 'sales1') |
| - mainframe type files migrated to Unix/Linux/Windows | |
| - Fixed-Field record layout converted to "delimited","format" | |
| as required for input to SQL databases |
| 9A2. | Executing uvcopy jobs to convert to fixed-field to "delimited","format" |
| 9A3. | uvcopy job 'delimcust1' to convert 'cust1' to 'cust1.txt' |
| - inserts "field","delimiters" |
| 9A4. | uvcopy job 'delimsales1' to convert 'sales1' to 'sales1.txt' |
| - inserts "field","delimiters", option for just ','s on numeric fields |
| 9A5. | uvcopy job 'undlmsales1' to convert 'sales1.txt' to tmp/sales1.fix |
| - undelimits, converts "delimited","format" to fixed field format |
| 9A6. | Converting Original mainframe EBCDIC sales1 file to ASCII |
| - converting zoned signs to 'separate sign leading' | |
| - inserting 'decimal points' (vs mainframe assumed/implied) |
| 9A7. | uvcopy job 'delimsales0' to convert original EBCDIC to delimited |
| - translate, reformat, delimit, etc |
| 9A8. | Using 'uvhd' to investigate original EBCDIC sales1 file |
| - can not use Unix/Linux utilities (vi, etc) to investigate EBCDIC files |
This info (about creating "delimited","files" from fixed length files) is at the back in Part_9 because not everybody will be interested. You do not need to know this if you just want to use the "delimited","files" to do the SQL tutorials in 'Part_1' & 'Part_3'.
If you are a UV Software customer & have Vancouver Utilities installed, these examples will help you to convert your fixed-length records to "delimited","format" for loading your database tables.
Do NOT confuse the 'uvcopy' jobs in Part_9 with the 'uycopy' jobs in Part_8. The 'uycopy' jobs access the Database/Tables directly via fili/filo typ=DBT.
The uvcopy jobs in Part_9 are used to convert Fixed-Field to "delimited", prior to loading a DB/Table using SQL, OR convert "delimited" to Fixed-Field, after unloading a table with SQL.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Test files 'cust1' & 'sales1' are provided in /home/uvadm/dat1/... These are mainframe type files with fixed record layouts & we will need to convert to delimited format before we can load into MySQL database tables.
I have listed the 2 files below, with the 'uvlist' utility & option 'y80' to print a column scale, which makes it easy to determine the record layout.
#0. Login uvadm --> /home/uvadm
===========================
#1. uvlist dat1/cust1 y80
=====================
/home/uvadm/dat1/cust1 now=081102:0953 uvadm pg# 1
1 2 3 4 5 6 7 8
12345678901234567890123456789012345678901234567890123456789012345678901234567890
130140 EVERGREEN MOTORS LTD. 1815 BOWEN ROAD NANAIMO BC 139923 JOHNSTONE BOILER 1250 EAST PENDER ST. VANCOUVER BC 150825 RIGGERS INDUSTRIAL 960 - 6TH AVENUE HOPE BC 201120 ALLTYPE RENTAL LTD. BOX 1819 DRAYTON VALLEY AL 308685 FOOTHILLS ELECTRIC 3932 - 3A ST. N.W. CALGARY AL 315512 PARTS PLUS BOX 510 MAIN ST. THREE HILLS AB 400002 ACKLANDS LTD 945 -2ND AVE. PRINCE GEORGE BC 406082 PRECAM RENTALS LTD. 10116-94TH AVE. FORT ST. JOHN BC
Layout: cust# 01-06, name 08-29, adrs 31-52, city 54-69, prov 71-72
#2. uvlist dat1/sales1 y80
======================
/home/uvadm/dat1/sales1 now=081102:0953 uvadm pg# 1
1 2 3 4 5 6 7 8
12345678901234567890123456789012345678901234567890123456789012345678901234567890
130140 21 2004-08-02 INV11201 HAM001 000010 00012.00 0000120.00 150825 44 2006-08-04 INV11202 HAM001 000011 00010.00 0000110.00 150825 44 2006-08-05 INV11203 HAX129 000012 00020.00 0000240.00 223240 65 2008-08-16 CRED9505 HAM001 -00013 00012.00 -000156.00 223240 65 2008-08-17 INV44103 BBQ001 000014 00100.00 0001400.00 308685 21 2009-08-14 CRED8835 TAB013 -00015 00200.00 -003200.00 308685 21 2009-08-12 INV66058 SAW051 000016 00150.00 0002250.00 406082 35 2002-08-15 INV33001 BBQ001 000017 00100.00 0001700.00 406082 35 2002-08-16 INV33001 TAB013 000018 00280.00 0005040.00 406082 65 2002-08-16 INV44199 HAM001 000019 00012.00 0000228.00
Layout: cust# 01-06, salesman 08-09, date 11-20, inv# 22-29, product 31-36
quantity 38-43, unit price 45-52, extended amount 54-63
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
The Vancouver utility 'uvcopy' makes it easy to convert mainframe type fixed- field format to the "delimited", format required to load SQL databases.
Here is how I created the fixed-field 'cust1' & 'sales1' files (listed above) to "delimited", files 'cust1.txt' & 'sales1.txt' (listed below).
#0. Login uvadm --> /home/uvadm
===========================
#1. uvcopy delimcust1,fili1=dat1/cust1,filo1=dat1/cust1.txt
=======================================================
130140,"EVERGREEN MOTORS LTD.","1815 BOWEN ROAD","NANAIMO","BC"
139923,"JOHNSTONE BOILER","1250 EAST PENDER ST.","VANCOUVER","BC"
150825,"RIGGERS INDUSTRIAL","960 - 6TH AVENUE","HOPE","BC"
201120,"ALLTYPE RENTAL LTD.","BOX 1819","DRAYTON VALLEY","AL"
308685,"FOOTHILLS ELECTRIC","3932 - 3A ST. N.W.","CALGARY","AL"
315512,"PARTS PLUS","BOX 510 MAIN ST.","THREE HILLS","AB"
400002,"ACKLANDS LTD","945 -2ND AVE.","PRINCE GEORGE","BC"
406082,"PRECAM RENTALS LTD.","10116-94TH AVE.","FORT ST. JOHN","BC"
#2. uvcopy delimsales1,fili1=dat1/sales1,filo1=dat1/sales1.txt
==========================================================
130140,21,2004-08-02,"INV11201","HAM001",000010,00012.00,0000120.00
150825,44,2006-08-04,"INV11202","HAM001",000011,00010.00,0000110.00
150825,44,2006-08-05,"INV11203","HAX129",000012,00020.00,0000240.00
223240,65,2008-08-16,"CRED9505","HAM001",-00013,00012.00,-000156.00
223240,65,2008-08-17,"INV44103","BBQ001",000014,00100.00,0001400.00
308685,21,2009-08-14,"CRED8835","TAB013",-00015,00200.00,-003200.00
308685,21,2009-08-12,"INV66058","SAW051",000016,00150.00,0002250.00
406082,35,2002-08-15,"INV33001","BBQ001",000017,00100.00,0001700.00
406082,35,2002-08-16,"INV33001","TAB013",000018,00280.00,0005040.00
406082,65,2002-08-16,"INV44199","HAM001",000019,00012.00,0000228.00
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
Here are the 'uvcopy jobs' used to create the "delimited", files listed above. 'uvcopy' is an interpretive utility that interprets the 'uvcopy jobs' (or 'parameter files' or 'instruction files'), with no compilation required.
These supplied parameter files (or 'pre-programmed jobs') are provided in /home/uvadm/pf/demo/... which is in the PFPATH searched by the uvcopy utility.
# delimcust1 - "delimit", customer name & address for SQL demo examples
# - by Owen Townsend, UV Software, November 2008
# - see www.uvsoftware.ca/sqldemo.htm
#
# uvcopy delimcust1 <-- execute this job (files default as shown below)
# ================= - sample I/O for 1st record in file below:
#
# cust# 01-06, name 08-29, adrs 31-52, city 54-69, prov 71-72
# 1 2 3 4 5 6 7
# 123456789012345678901234567890123456789012345678901234567890123456789012
#
# 130140 EVERGREEN MOTORS LTD. 1815 BOWEN ROAD NANAIMO BC
#
# 130140,"EVERGREEN MOTORS LTD.","1815 BOWEN ROAD","NANAIMO","BC"
#
fili1=?dat1/cust1.fix,rcs=80,typ=LST
filo1=?dat1/cust1.txt,rcs=80,typ=LSTt
@run
opn all
# begin loop to process each record - until EOF
man20 get fili1,a0 get next record
skp> eof (cc set > at EOF)
# store fields 100 bytes apart for 'dlm' insert ","
mvc b0(6),a0 cust#
mvc b100(22),a7 name
mvc b200(22),a30 address
mvc b300(16),a53 city
mvc b400(2),a70 province
dlmn1 c0(100),b0(100),5 delimit 5*100 fields from area b to c
# =========================
put filo1,c0 write output record
skp man20 repeat loop
# EOF - close files & end job
eof cls all
eoj
The critical instruction is 'dlm', which converts a series of fixed 'maximum' length fields to the "delimited", format. Op1 'c0(100)' defines the output area, op2 'b0(100)' defines the 1st max lth field, op3 specifies the number of fields, & op4 defines the delimiter.
The preceding instructions reformat the incoming 'actual length' fields to the 'maximum length' fields. We use 100 bytes as the maximum, because that makes it easy to code the 'mvc' instruction addresses.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
# delimsales1 - "delimit", customer sales test file for SQL demo examples
# - by Owen Townsend, UV Software, November 2008
# - see www.uvsoftware.ca/sqldemo.htm
#
# uvcopy delimsales1 <-- execute this job (files default as shown below)
# ================== - see 1st record of Input/Output below:
#
# cust# 01-06, salesman 08-09, date 11-20, inv# 22-29, product 31-36 <-- layout
# quantity 38-43, unit price 45-52, extended amount 54-63
#
# 130140 21 2004-08-02 INV11201 HAM001 000010 00012.00 0000120.00 <-- Input
#
# 130140,21,2004-08-02,"INV1120","HAM001",000010,00012.00,0000120.00 <-- Output
#
fili1=?dat1/sales1.fix,rcs=64,typ=RSF
filo1=?dat1/sales1.txt,rcs=80,typ=LSTt
@run
opn all
#
# begin loop to process each record - until EOF
man20 get fili1,a0 get next record
skp> eof (cc set > at EOF)
mvc b0(6),a0 cust#
mvc b100(2),a7 salesman
mvc b200(10),a10 date
mvc b300(8),a21 invoice#
mvc b400(6),a30 product code
mvc b500(6),a37 quantity
mvc b600(8),a44 unit price
mvc b700(10),a53 extended amount
dlmn1 c0(100),b0(100),8 delimit 8*100 fields from area b to c
# =========================
put filo1,c0 write output record
skp man20 repeat loop
#
# EOF - close files & end job
eof cls all
eoj
130140,21,2004-08-02,"INV11201","HAM001",000020,00020.00,0000400.00
===================================================================
Here is just the 1st line of the 'sales1.txt' file (all lines on page '9A2').
You can see that the fields with 'ALPHA' characters are surrounded by "double quotes", but the 'NUMERIC' fields are not. This is caused by the 'n1' option on 'dlmn1'. All instructions are 3 characters & anything following the 3rd character is an option.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
This job is listed here in case you are interested in seeing how you could convert "delimited","format" back to Fixed-Field format.
The 'und' instruction converts to a MAXIMUM field length, followed by a series of 'mvc' instructions to create the desired output record layout.
# undlmsales1 - undelimit "sales1.txt" test/demo file
# - using 'und' instrn to convert "delimited", to Fixed-Field
# - by Owen Townsend, UV Software, November 2008
# - see www.uvsoftware.ca/sqldemo.htm
#
# uvcopy undlmsales1 <-- execute this job (files default as shown below)
# ================== - opposite of 'delimsales1'
# - see 1st record of Input/Output files below:
#
# 130140,21,2004-08-02,"INV1120","HAM001",000010,00012.00,0000120.00 <-- Input
#
# 130140 21 2004-08-02 INV11201 HAM001 000010 00012.00 0000120.00 <-- Output
#
# cust# 01-06, salesman 08-09, date 11-20, inv# 22-29, product 31-36 <-- layout
# quantity 38-43, unit price 45-52, extended amount 54-63
#
fili1=?dat1/sales1.txt,rcs=80,typ=LST
filo1=?tmp/sales1.fix,rcs=64,typ=RST
@run
opn all
#
# begin loop to process each record - until EOF
man20 get fili1,a0 get next record
skp> eof (cc set > at EOF)
#
und b0(100),a0(100),8 undelimit 8 fields from area a to b (8*100)
# =========================
# move data from fixed max lth fields to desired output layout (from b to c)
mvc c0(6),b0 cust#
mvc c7(2),b100 salesman
mvc c10(10),b200 date
mvc c21(8),b300 invoice#
mvc c30(6),b400 product code
mvc c37(6),b500 quantity
mvc c44(8),b600 unit price
mvc c53(10),b700 extended amount
put filo1,c0 write output record
skp man20 repeat loop
#
# EOF - close files & end job
eof cls all
eoj
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
The preceding delimit jobs assumed the data files had already been translated from the mainframe EBCDIC to ASCII files with numeric fields converted from 'zoned signs' to 'separate leading signs' & with decimal points inserted.
Here is the original file. We show it translated to ASCII (so you can read it). Numeric field signs still reflect the mainframe conventions:
EBCDIC zone + sign is x'C0', + 0123456789 becomes {ABCDEFGHI
EBCDIC zone - sign is x'D0', - 0123456789 becomes }JKLMNOPQR
1 2 3 4 5 6
123456789012345678901234567890123456789012345678901234567890
130140 21 20040802 INV11201 HAM001 00001{ 0001200 00001200{
150825 44 20060804 INV11202 HAM001 00001A 0001000 00001100{
150825 44 20060805 INV11203 HAX129 00001B 0002000 00002400{
223240 65 20080816 CRED9505 HAM001 00001L 0001200 00001560} <-- neg 3='L',0='}'
223240 65 20080817 INV44103 BBQ001 00001D 0010000 00014000{
308685 21 20090814 CRED8835 TAB013 00001N 0020000 00032000} <-- neg 5='N',0='}'
308685 21 20090812 INV66058 SAW051 00001F 0015000 00022500{
406082 35 20020815 INV33001 BBQ001 00001G 0010000 00017000{
406082 35 20020816 INV33001 TAB013 00001H 0028000 00050400{
406082 65 20020816 INV44199 HAM001 00001I 0001200 00002280{
Compare this file to dat1/sales1.fix on page '9A1' which shows the same file after translation to ASCII, signs converted from zones to 'separate signs leading',& decimal points inserted. We will illustrate 1st negative record from both files:
223240 65 20080816 CRED9505 HAM001 00001L 0001200 00001560} <-- mainframe signs ===========================================================
223240 65 2008-08-16 CRED9505 HAM001 -00013 00012.00 -000156.00 <-- ASCII style =============================================================== SQL standards
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
# delimsales0 - "delimit", original EBCDIC customer sales1 file for SQL demos
# - by Owen Townsend, UV Software, November 2008
# - see www.uvsoftware.ca/sqldemo.htm
#
# Compare this delimsales0 to delimsales1 which assumed file already converted
# from mainframe (EBCDIC, signs in zones, decimal points assumed)
#
# uvcopy delimsales0 <-- execute this job (files default as shown below)
# ================== - see 1st record of Input/Output below:
#
# cust# 01-06, salesman 08-09, date 11-18, inv# 20-27, product 29-34 <-- layout
# quantity 36-41, unit price 43-49, extended amount 51-59
#
# 130140 21 20040802 INV11201 HAM001 000010 0001200 000012000 <-- Input
#
# 130140,21,2004-08-02,"INV1120","HAM001",000010,00012.00,0000120.00 <-- Output
#
fili1=?dat1/sales1_origE,rcs=60,typ=RSF
filo1=?dat1/sales1.txt,rcs=80,typ=LSTt
@run
opn all
#
# begin loop to process each record - until EOF
man20 get fili1,a0 get next record
skp> eof (cc set > at EOF)
tra a0(64) translate EBCDIC to ASCII
mvc b0(6),a0 cust#
mvc b100(2),a7 salesman
edt b200(10),a10(8),'9999-99-99' date (insert '-'s for SQL)
mvc b300(8),a19 invoice#
mvc b400(6),a28 product code
mvc b500(-6),a35(6ze) qty (cnvt Zoned EBCDIC signs to leading)
edt b600(8),a42(7),'99999.99' price (insert decimal point)
edt b700(10),a50(9ze),'-999999.99' amount (leading sign & dcml pt)
dlmn1 c0(100),b0(100),8 delimit 8*100 fields from area b to c
# =========================
put filo1,c0 write output record
skp man20 repeat loop
#
# EOF - close files & end job
eof cls all
eoj
Compare this job 'delimsales0' to 'delimsales1' (listed on page '9A4').
| delimsales1 |
|
| delimsales0 |
|
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page
If you did want to investigate the original EBCDIC file, you can NOT do with 'vi', but you can use 'uvhd'. uvhd is a FREE utility from UV Software for investigating files with non-ASCII data (packed, binary, EBCDIC, etc). You can download uvhd from https://www.uvsoftware.ca/freestuff.htm
uvhd will display the 1st record & prompt for commands to browse, search, etc:
uvhd dat1/sales1_origE r60a <-- investigate EBCDIC file with 'uvhd'
========================== - options 'r60a', r60 = recsize
- option 'a' to translate char line to ASCII
10 20 30 40 50 60
r# 1 0123456789012345678901234567890123456789012345678901234567890123
0 130140 21 20040802 INV11201 HAM001 00001{ 0001200 00001200{.
FFFFFF4FF4FFFFFFFF4CDEFFFFF4CCDFFF4FFFFFC4FFFFFFF4FFFFFFFFC0
13014002102004080209551120108140010000010000012000000012000A
rec#=1 rcount=10 rsize=60 fsize=600 dat1/sales1_origE null=next,r#=rec,s=search,u=update,x=rollback,p=print,i=iprint,w=write,e=count ,g=genseq#,c=chkseq#,t=translate(ta=Asc,te=Ebc,tu=Upr,tl=Lwr,tc=Chars,tp=Pers) ,R#=Recsize,h1=char,h2=hex,q=quit,?=help --> q <-- quit after 1st record
uvhd displays data in 3 line groups (characters, hex-zones,& hex-digits). We specified option 'a' to translate the character line to ASCII, so we can read it. The actual data is displayed in vertical hexadecimal on the 2nd & 3rd lines (hex-zones & hex-digits).
The 1st byte (displayed as an ASCII 1 on the translated character line) is actually x'F1' zone 'F_' & digit '_1'. The letter 'I' is x'C9' zone C, digit 9.
Goto: Begin this doc , End this doc , Index this doc , Contents this library , UVSI Home-Page