Re: SQL access from Cobol Test
- From: "Pete Dashwood" <dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 10 Jan 2009 00:15:56 +1300
tleaders...gmail.com wrote:
The other day Pete said
Performance is largely taken care of by multiple cores running
separate
instances of objects (I did some experiments yesterday with a Data
Access
Layer object, fetching on a SQL cursor in COBOL. Where the single
object
instance was doing ALL actions (including updates and writes, as well
as
sequential and random access), the throughput on sequential access
was
around 40 rows a second... fairly poor, and really just marginal for
the
application which requires it. As soon as a second instance was
created
which handled all the updates, the sequential access hit 90 rows a
second. I
would stress that this involved NO CHANGE to logic in the class. They
were
just two instances of the same Class, one being invoked for PUT
actions and
the other for GET actions. They were running on a dual core machine
but I
suspect the process was not spread over separate cores (that is
supposed to
be coming with Windows 7 :-)) The point is that objects can be EASILY
parallell processed so performance of them is much less problematic
than
with procedural code.
I generally agree with Pete but in this case I think I will take
exception. 90 or 120 row per second would not be acceptable in any
environment.
It really depends on the processing load and what alternatives are
available. For example, if the alternative was to process manually and take
3 minutes per "row" then the above would be a very acceptable alternative.
:-)
After I posted that I thought again that I should be able to improve this
throughput.
I did. The same code now returns between 220 and 250 rows per second. While
this is still not exceptional, considering the processing load, it is
acceptable. It is way better than before... :-) Part of the problem was that
it was running in debug mode, but most of the problem was in executing a
couple of subroutines that were intended solely for debugging and which
wrote images and status to disk for every row fetched... :-)
I still don't like embedded SQL and cursors, but sometimes we are
constrained by things outside our control.
The problem is not in the procedural code it is in the
data access layer.
In my case it was in both, but I take your point...
In my option, If you use cursors in sql you will
never get the speed that you need.
I agree...
So I put together this test, and
included all the source code. I tried to write an exec sql process
but it was so slow that I assumed that I made a mistake somewhere. If
someone can add the exec and post their times here I would know how
far off my tests are. And thanks.
Do you mean embedded SQL by "exec SQL process"?
Also my assess layer contains all the data access verbs in a single
object, using a single connection to the data base. In this case
performance comes from having sql table indexed and structured for
speed.
Good database design will always trump poor database design.
GENERIC_TABLE_TEST - SQL code written for Query Analyzer, this
code will build a sql table with 500,000 records. The indexes are
created after the data file is built. This process took 3:05 min.
When I raw a separate test in which I built the indexes first this
process took 5:56. That is the penalty for writing to a table with
indexes.
READWRITETEST - VB program that will read and export the sql table
twice. The first process will read the table using a single sql
statement and a sqldatareader, time 9 sec for 500,000 records. The
second process uses the same data access components as the Cobol
programs, time 9 sec for 500,000 records.
O-GENERIC-TABLE ? procedural Cobol program to export data from sql
table to a text file using a data access component time 12 sec for
500,000 records.
I-GENERIC-TABLE ? procedural Cobol program to import new records from
a text file into a sql table, time 9.5 min for 500,000 records. My
table is optimized for reading, adding indexes to a sql table will
speed up the reads at the expense of the inserts.
These are the actual times for the test.
C:\IINRIS\UNISYS>CALL O-GENERIC-TABLE
O-GENERIC-TA: TODAY IS 1/08/2009 JAN 8,2009 THURSDAY
START TIME IS 21:45:13
0500000 RECORDS WERE PROCESSED
END TIME IS 21:45:25
C:\IINRIS\UNISYS>CALL \ATT\IINRIS\RELEASE\READWRITETEST
Start Time Using Data Set 9:45:26 PM
Records Written 500000
End Time Using Data Set 9:45:35 PM
Start Time Using Standard Files 9:45:35 PM
Records Written 500000
End Time Using Standard Files 9:45:44 PM
C:\IINRIS\UNISYS>CALL I-GENERIC-TABLE
I-GENERIC-TA: TODAY IS 1/08/2009 JAN 8,2009 THURSDAY
START TIME IS 21:45:45
0500000 RECORDS WERE PROCESSED
END TIME IS 21:55:03
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
--*GENERIC_TABLE_TEST
---%Drop Index GENERIC_TABLE
IF exists (SELECT * FROM dbo.sysINDEXES where NAME =
'IGENERIC_TABLEkeyvalue' )
DROP INDEX GENERIC_TABLE.IGENERIC_TABLEkeyvalue
GO
IF exists (SELECT * FROM dbo.sysINDEXES where NAME =
'IGENERIC_TABLE_prime' )
DROP INDEX GENERIC_TABLE.IGENERIC_TABLE_prime
GO
IF exists (SELECT * FROM dbo.sysINDEXES where NAME =
'IGENERIC_TABLE0' )
DROP INDEX GENERIC_TABLE.IGENERIC_TABLE0
GO
IF exists (SELECT * FROM dbo.sysINDEXES where NAME =
'IGENERIC_TABLEall' )
DROP INDEX GENERIC_TABLE.IGENERIC_TABLEall
GO
---%Drop Trigger GENERIC_TABLE
IF exists (SELECT * FROM dbo.sysobjects where name =
'TR_GENERIC_TABLE' and xtype = 'TR')
drop trigger TR_GENERIC_TABLE
GO
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'IGENERIC_TABLEprime'
AND XTYPE = 'PK')
ALTER TABLE [dbo].GENERIC_TABLE DROP CONSTRAINT IGENERIC_TABLEprime
GO
---%Drop Table GENERIC_TABLE
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].
[GENERIC_TABLE]') AND OBJECTPROPERTY(ID, N'IsUserTable') = 1)
DROP TABLE [DBO].[GENERIC_TABLE]
GO
---%Create Table GENERIC_TABLE
IF not exists (SELECT * FROM dbo.sysobjects where id = object_id
(N'[dbo].[GENERIC_TABLE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[GENERIC_TABLE]
(
GEN_CHAR CHAR (30) Not Null,
GEN_INT INT Not Null,
GEN_DATE INT Not Null,
GEN_DECIMAL DECIMAL (8,2) Not Null,
GEN_CHAR2 CHAR (30) Null,
GEN_INT2 INT Null,
GEN_DATE2 INT Null,
GEN_DECIMAL2 DECIMAL (8,2) Null,
IDI int IDENTITY (1, 1) NOT NULL
)
GO
---%Load Table GENERIC_TABLE
declare @daycount INT
declare @todaysdatePlus INT
set @daycount = 1
while @daycount < 50001
begin
SET @todaysdatePlus = (SELECT CONVERT(INT,CONVERT(varchar
(8),dateadd(d,@daycount,GETDATE()),112)))
insert into GENERIC_TABLE (GEN_CHAR, GEN_INT, GEN_DATE,
GEN_DECIMAL, GEN_CHAR2, GEN_INT2, GEN_DATE2, GEN_DECIMAL2)
VALUES ('A',(@daycount*100)+1,@todaysdatePlus,100.00,'a1',
1,@todaysdatePlus,1.11)
insert into GENERIC_TABLE (GEN_CHAR, GEN_INT, GEN_DATE,
GEN_DECIMAL, GEN_CHAR2, GEN_INT2, GEN_DATE2, GEN_DECIMAL2)
VALUES ('B',(@daycount*100)+2,@todaysdatePlus,100.01,'A2',
1,@todaysdatePlus,2.22)
insert into GENERIC_TABLE (GEN_CHAR, GEN_INT, GEN_DATE,
GEN_DECIMAL, GEN_CHAR2, GEN_INT2, GEN_DATE2, GEN_DECIMAL2)
VALUES ('C',(@daycount*100)+3,@todaysdatePlus,100.02,'A2',
1,@todaysdatePlus,3.33)
insert into GENERIC_TABLE (GEN_CHAR, GEN_INT, GEN_DATE,
GEN_DECIMAL, GEN_CHAR2, GEN_INT2, GEN_DATE2, GEN_DECIMAL2)
VALUES ('D',(@daycount*100)+1,@todaysdatePlus,100.03,'A2',
1,@todaysdatePlus,4.44)
insert into GENERIC_TABLE (GEN_CHAR, GEN_INT, GEN_DATE,
GEN_DECIMAL, GEN_CHAR2, GEN_INT2, GEN_DATE2, GEN_DECIMAL2)
VALUES ('E',(@daycount*100)+1,@todaysdatePlus,100.04,'a2',
1,@todaysdatePlus,5.55)
insert into GENERIC_TABLE (GEN_CHAR, GEN_INT, GEN_DATE,
GEN_DECIMAL, GEN_CHAR2, GEN_INT2, GEN_DATE2, GEN_DECIMAL2)
VALUES ('F',(@daycount*100)+1,@todaysdatePlus,100.05,'A2',
1,@todaysdatePlus,6.66)
insert into GENERIC_TABLE (GEN_CHAR, GEN_INT, GEN_DATE,
GEN_DECIMAL, GEN_CHAR2, GEN_INT2, GEN_DATE2, GEN_DECIMAL2)
VALUES ('A',(@daycount*100)+5,@todaysdatePlus,100.06,'A2',
1,@todaysdatePlus,7.77)
insert into GENERIC_TABLE (GEN_CHAR, GEN_INT, GEN_DATE,
GEN_DECIMAL, GEN_CHAR2, GEN_INT2, GEN_DATE2, GEN_DECIMAL2)
VALUES ('A',(@daycount*100)+6,@todaysdatePlus,100.07,'A2',
1,@todaysdatePlus,8.88)
insert into GENERIC_TABLE (GEN_CHAR, GEN_INT, GEN_DATE,
GEN_DECIMAL, GEN_CHAR2, GEN_INT2, GEN_DATE2, GEN_DECIMAL2)
VALUES ('A',(@daycount*100)+7,@todaysdatePlus,100.08,'A2',
1,@todaysdatePlus,9.99)
insert into GENERIC_TABLE (GEN_CHAR, GEN_INT, GEN_DATE,
GEN_DECIMAL, GEN_CHAR2, GEN_INT2, GEN_DATE2, GEN_DECIMAL2)
VALUES ('B',(@daycount*100)+7,@todaysdatePlus,100.09,'A2',
1,@todaysdatePlus,10.10)
set @daycount = @daycount + 1
end
GO
---%Create Index GENERIC_TABLE
IF not exists (SELECT * FROM dbo.sysINDEXES where NAME =
'IGENERIC_TABLE_prime' )
CREATE UNIQUE INDEX IGENERIC_TABLE_prime ON GENERIC_TABLE
(GEN_CHAR, GEN_INT, GEN_DATE, GEN_DECIMAL)
GO
--- IF not exists (SELECT * FROM dbo.sysINDEXES where NAME =
'IGENERIC_TABLEall' )
--- CREATE UNIQUE INDEX IGENERIC_TABLEALL ON GENERIC_TABLE (GEN_CHAR,
GEN_INT, GEN_DATE, GEN_DECIMAL, GEN_CHAR2, GEN_INT2, GEN_DATE2,
GEN_DECIMAL2,IDI)
--- GO
---%Select count GENERIC_TABLE
--- select COUNT(*) from generic_table
--- select * from generic_table
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
''READWRITETEST TRANSFERRED TO DOCUMENTATION ON
1/08/09
Imports File_System
Module READWRITETEST
Sub Main()
'#ReadWriteTest - Export Generic Table
'= ReadWriteTest - Export Generic Table
'= This program will export the Generic Table as a test
between using
'= file_system or direct queries.
'=
Console.OpenStandardOutput()
Dim oFile As System.IO.File
Dim oWrite As System.IO.StreamWriter
Dim sSqlNew As String
Dim workline As String
Dim iRecordCnt As Integer
Dim SQL_GEN_CHAR As String 'CHAR (30),
Dim SQL_GEN_INT As Integer '8,0
Dim SQL_GEN_DATE As Integer '8,0
Dim SQL_GEN_DECIMAL As Decimal ' (8,2),
Dim SQL_GEN_CHAR2 As String 'CHAR (30),
Dim SQL_GEN_INT2 As Integer '8,0
Dim SQL_GEN_DATE2 As Integer '8,0
Dim SQL_GEN_DECIMAL2 As Decimal ' (8,2),
''Console.WriteLine("Start Time Create New Data " &
DateTime.Now.ToLongTimeString)
''GENERIC_TABLE_TEST_ALLFunctionNames()
''Console.WriteLine(" End Time Create New Data " &
DateTime.Now.ToLongTimeString)
''Console.WriteLine(" ")
Console.WriteLine("Start Time Using Data Set " &
DateTime.Now.ToLongTimeString)
oWrite = oFile.CreateText("C:\temp\GENERIC_TABLE_A.txt")
iRecordCnt = 0
sSqlNew = "SELECT GEN_CHAR, GEN_INT, GEN_DATE, GEN_DECIMAL,
GEN_CHAR2, GEN_INT2, GEN_DATE2, GEN_DECIMAL2 " & vbCrLf
sSqlNew = sSqlNew & "FROM GENERIC_TABLE " & vbCrLf
''sSqlNew = sSqlNew & " WHERE GEN_CHAR = 'A' " & vbCrLf
Dim myData As SqlClient.SqlDataReader
myData = GetDataReader(sSqlNew)
If Not myData Is Nothing Then
If myData.HasRows Then
Do While myData.Read = True
If myData.IsDBNull(0) Then SQL_GEN_CHAR = " " Else
SQL_GEN_CHAR = myData.GetString(0)
If myData.IsDBNull(1) Then SQL_GEN_INT = 0 Else
SQL_GEN_INT = myData.GetInt32(1)
If myData.IsDBNull(2) Then SQL_GEN_DATE = 0 Else
SQL_GEN_DATE = myData.GetInt32(2)
If myData.IsDBNull(3) Then SQL_GEN_DECIMAL = 0
Else SQL_GEN_DECIMAL = myData.GetDecimal(3)
If myData.IsDBNull(4) Then SQL_GEN_CHAR2 = " "
Else SQL_GEN_CHAR2 = myData.GetString(4)
If myData.IsDBNull(5) Then SQL_GEN_INT2 = 0 Else
SQL_GEN_INT2 = myData.GetInt32(5)
If myData.IsDBNull(6) Then SQL_GEN_DATE2 = 0 Else
SQL_GEN_DATE2 = myData.GetInt32(6)
If myData.IsDBNull(7) Then SQL_GEN_DECIMAL2 = 0
Else SQL_GEN_DECIMAL2 = myData.GetDecimal(7)
iRecordCnt += 1
workline = ""
workline = workline & SQL_GEN_CHAR.PadRight(30) &
"|" 'As String
workline = workline & SQL_GEN_INT.ToString.PadLeft
(9) & "|"
workline = workline &
SQL_GEN_DATE.ToString.PadLeft
(9) & "|"
workline = workline &
SQL_GEN_DECIMAL.ToString.PadLeft(10) & "|"
workline = workline & SQL_GEN_CHAR2.PadRight(30) &
"|" 'As String
workline = workline &
SQL_GEN_INT2.ToString.PadLeft
(9) & "|"
workline = workline &
SQL_GEN_DATE2.ToString.PadLeft(9) & "|"
workline = workline &
SQL_GEN_DECIMAL2.ToString.PadLeft(10) & "|"
oWrite.WriteLine(workline)
Loop
End If
End If
myData.Close()
oWrite.Close()
Console.WriteLine("Records Written " & iRecordCnt.ToString)
Console.WriteLine(" End Time Using Data Set " &
DateTime.Now.ToLongTimeString)
Console.WriteLine(" ")
Console.WriteLine("Start Time Using Standard Files " &
DateTime.Now.ToLongTimeString)
oWrite = oFile.CreateText("C:\temp\GENERIC_TABLE_B.txt")
iRecordCnt = 0
Dim XGeneric As New File_System.GENERIC_TABLE
XGeneric.GENERIC_TABLE_INPUT()
XGeneric.GENERIC_TABLE_Set_All_Fields_To_Null()
''XGeneric.GENERIC_TABLE_GET_WHERE = "GEN_CHAR = 'A'"
If XGeneric.GENERIC_TABLE_START_NOT_LESS = 0 Then
While XGeneric.GENERIC_TABLE_READ_NEXT = 0
With XGeneric
SQL_GEN_CHAR = .GEN_CHAR
SQL_GEN_INT = .GEN_INT
SQL_GEN_DATE = .GEN_DATE
SQL_GEN_DECIMAL = .GEN_DECIMAL
SQL_GEN_CHAR2 = .GEN_CHAR2
SQL_GEN_INT2 = .GEN_INT2
SQL_GEN_DATE2 = .GEN_DATE2
SQL_GEN_DECIMAL2 = .GEN_DECIMAL2
End With
iRecordCnt += 1
workline = ""
workline = workline & SQL_GEN_CHAR.PadRight(30) & "|"
workline = workline & SQL_GEN_INT.ToString.PadLeft(9)
& "|"
workline = workline & SQL_GEN_DATE.ToString.PadLeft(9)
& "|"
workline = workline & SQL_GEN_DECIMAL.ToString.PadLeft
(10) & "|"
workline = workline & SQL_GEN_CHAR2.PadRight(30) & "|"
workline = workline & SQL_GEN_INT2.ToString.PadLeft(9)
& "|"
workline = workline & SQL_GEN_DATE2.ToString.PadLeft
(9) & "|"
workline = workline &
SQL_GEN_DECIMAL2.ToString.PadLeft
(10) & "|"
oWrite.WriteLine(workline)
End While
End If
XGeneric.GENERIC_TABLE_CLOSE()
XGeneric = Nothing
oWrite.Close()
Console.WriteLine("Records Written " & iRecordCnt.ToString)
Console.WriteLine(" End Time Using Standard Files " &
DateTime.Now.ToLongTimeString)
Console.WriteLine(" ")
'=
End Sub
End Module
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
000010**I-GENERIC-TABLE TRANSFERRED TO DOCUMENTATION ON
1/08/09
000020 IDENTIFICATION DIVISION.
000030 PROGRAM-ID. I-GENERIC-TABLE.
000040 AUTHOR. TOM LEADERS.
000050 DATE-WRITTEN. 01/08/2009.
000060 DATE-COMPILED.
000070******************************************************************
000080******************************************************************
000090** THIS PROGRAM IS THE PROPERTY OF TOM LEADERS
**
000100******************************************************************
000110******************************************************************
000120************************************************************ ***
000130*= I-GENERIC-TABLE - FILE LAYOUT CHANGE FOR FDGEN.
000140*= GENERIC-TABLE CONVERSION FROM FLAT FILE => SQL TABLE.
000150*=
000160 ENVIRONMENT DIVISION.
000170 CONFIGURATION SECTION.
000180 SOURCE-COMPUTER. FUJITSU NET.
000190 OBJECT-COMPUTER. FUJITSU NET.
000200 SPECIAL-NAMES.
000210 ARGUMENT-VALUE IS COMMAND-LINE.
000220 REPOSITORY.
000230 COPY "C:\IINRIS\UNISYS\LIB\CL-RP-FDGEN.CBL".
000240 CLASS CommonVB AS "File_System.CommonVB".
000250
000260 INPUT-OUTPUT SECTION.
000270 FILE-CONTROL.
000280
000290 SELECT NEW-GENERIC-TABLE
000300 ASSIGN TO "C:\TEMP\GENERIC-TABLE.TXT"
000310 ORGANIZATION IS LINE SEQUENTIAL
000320 ACCESS MODE IS SEQUENTIAL.
000330
000340 DATA DIVISION.
000350 FILE SECTION.
000360
000370 FD NEW-GENERIC-TABLE.
000380 01 NEW-GENERIC-REC.
000390 05 NGEN-CHAR PIC X(30).
000400 05 FILLER PIC X.
000410 05 NGEN-INT PIC -(8)9.
000420 05 FILLER PIC X.
000430 05 NGEN-DATE PIC -(8)9.
000440 05 FILLER PIC X.
000450 05 NGEN-DECIMAL PIC -(6)9.9(2).
000460 05 FILLER PIC X.
000470 05 NGEN-CHAR2 PIC X(30).
000480 05 FILLER PIC X.
000490 05 NGEN-INT2 PIC -(8)9.
000500 05 FILLER PIC X.
000510 05 NGEN-DATE2 PIC -(8)9.
000520 05 FILLER PIC X.
000530 05 NGEN-DECIMAL2 PIC -(6)9.9(2).
000540 05 FILLER PIC X.
000550
000560 WORKING-STORAGE SECTION.
000570 77 WS-PROG-NAME PIC X(15) VALUE "I-GENERIC-TABLE".
000580 77 WS-INSTALATION PIC X(33) VALUE SPACES.
000590 77 A PIC 9(3) VALUE 0.
000600 77 WRITE-CTR PIC 9(7) VALUE 0.
000610
000620 COPY "C:\IINRIS\UNISYS\LIB\WS-DATE.CBL".
000630
000640 01 DISPLAY-TIME.
000650 05 DT-T-HH PIC 99.
000660 05 FILLER PIC X VALUE ":".
000670 05 DT-T-MM PIC 99.
000680 05 FILLER PIC X VALUE ":".
000690 05 DT-T-SS PIC 99.
000700** 05 FILLER PIC X VALUE ".".
000710** 05 DT-T-TT PIC 99.
000720
000730 01 VBOBJ OBJECT REFERENCE CommonVB.
000740 COPY "C:\IINRIS\UNISYS\LIB\CL-WS-FDGEN.CBL".
000750
000760 PROCEDURE DIVISION.
000770 DECLARATIVES.
000780 ERROR-1 SECTION.
000790 USE AFTER STANDARD ERROR PROCEDURE ON NEW-GENERIC-TABLE.
000800 ERROR-1A.
000810 DISPLAY "INVALID READ FROM IMPORT TEXT FILE".
000820 DISPLAY "PROGRAM ABORT ".
000830 STOP RUN.
000840 END DECLARATIVES.
000850 000-AFTER-DCL SECTION.
000860 START-IT-ALL.
000870 INVOKE CommonVB "NEW" RETURNING VBOBJ.
000880 COPY "C:\IINRIS\UNISYS\LIB\PR-DISDT.CBL".
000890 COPY "C:\IINRIS\UNISYS\LIB\CL-II-FDGEN.CBL".
000900
000910 ACCEPT WS-TIME FROM TIME.
000920 MOVE WS-T-HH TO DT-T-HH.
000930 MOVE WS-T-MM TO DT-T-MM.
000940 MOVE WS-T-SS TO DT-T-SS.
000950** MOVE WS-T-TT TO WS-T-TT.
000960 DISPLAY "START TIME IS " DISPLAY-TIME.
000970
000980
000990 PERFORM ZGENERIC-TABLE-OUTPUT.
001000 OPEN INPUT NEW-GENERIC-TABLE.
001010
001020
001030 READ-GENERIC-TABLE-LOOP.
001040 READ NEW-GENERIC-TABLE AT END
001050 GO TO PROG-GENERIC-TABLE-END.
001060 ADD 1 TO WRITE-CTR.
001070
001080 MOVE-GENERIC-REC.
001090 INITIALIZE GENERIC-REC.
001100 MOVE NGEN-CHAR TO GEN-CHAR.
001110 MOVE NGEN-INT TO GEN-INT.
001120 MOVE NGEN-DATE TO GEN-DATE.
001130 MOVE NGEN-DECIMAL TO GEN-DECIMAL.
001140 MOVE NGEN-CHAR2 TO GEN-CHAR2.
001150 MOVE NGEN-INT2 TO GEN-INT2.
001160 MOVE NGEN-DATE2 TO GEN-DATE2.
001170 MOVE NGEN-DECIMAL2 TO GEN-DECIMAL2.
001180 PERFORM ZGENERIC-TABLE-WRITE.
001190 IF GENERIC-TABLE-INV-READ = 1
001200 DISPLAY "INVALID WRITE".
001210
001220 GO TO READ-GENERIC-TABLE-LOOP.
001230
001240
001250 COPY "C:\IINRIS\UNISYS\LIB\CL-PR-FDGEN.CBL".
001260
001270 PROG-GENERIC-TABLE-END.
001280 CLOSE NEW-GENERIC-TABLE .
001290 PERFORM ZGENERIC-TABLE-CLOSE.
001300 DISPLAY WRITE-CTR " RECORDS WERE PROCESSED".
001310 ACCEPT WS-TIME FROM TIME.
001320 MOVE WS-T-HH TO DT-T-HH.
001330 MOVE WS-T-MM TO DT-T-MM.
001340 MOVE WS-T-SS TO DT-T-SS.
001350** MOVE WS-T-TT TO WS-T-TT.
001360 DISPLAY " END TIME IS " DISPLAY-TIME.
001370
001380 SET VBOBJ TO NULL
001390 STOP RUN.
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
000010**O-GENERIC-TABLE TRANSFERRED TO DOCUMENTATION ON
1/08/09
000020 IDENTIFICATION DIVISION.
000030 PROGRAM-ID. O-GENERIC-TABLE.
000040 AUTHOR. TOM LEADERS.
000050 DATE-WRITTEN. 01/08/2009.
000060 DATE-COMPILED.
000070******************************************************************
000080******************************************************************
000090** THIS PROGRAM IS THE PROPERTY OF TOM LEADERS
**
000100******************************************************************
000110******************************************************************
000120**SA;CO S/LAB/O-GENERIC-TABLES01 AS O-GENERIC-TABLE *****
000130************************************************************ ***
000140*= O-GENERIC-TABLE - FILE LAYOUT CHANGE FOR FDGEN.
000150*= GENERIC-TABLE CONVERSION FROM SQL TABLE => FLAT FILE.
000160*=
000170 ENVIRONMENT DIVISION.
000180 CONFIGURATION SECTION.
000190 SOURCE-COMPUTER. A-SERIES.
000200 OBJECT-COMPUTER. A-SERIES.
000210 SPECIAL-NAMES.
000220 ARGUMENT-VALUE IS COMMAND-LINE.
000230
000240 REPOSITORY.
000250 COPY "C:\IINRIS\UNISYS\LIB\CL-RP-FDGEN.CBL".
000260 CLASS SYS-STRING AS "System.String"
000270 CLASS CommonVB AS "File_System.CommonVB".
000280
000290 INPUT-OUTPUT SECTION.
000300 FILE-CONTROL.
000310
000320*= COPY "C:\IINRIS\UNISYS\LIB\SE-FDGEN.CBL".
000330
000340 SELECT NEW-GENERIC-TABLE
000350 ASSIGN TO WS-ID
000360 ORGANIZATION IS LINE SEQUENTIAL
000370 ACCESS MODE IS SEQUENTIAL.
000380
000390
000400 DATA DIVISION.
000410 FILE SECTION.
000420*= COPY "C:\IINRIS\UNISYS\LIB\FD-FDGEN.CBL".
000430
000440 FD NEW-GENERIC-TABLE.
000450*= LABEL RECORDS ARE STANDARD.
000460*= VALUE OF TITLE IS WS-ID.
000470*= DEPENDENTSPECS IS TRUE.
000480 01 NEW-GENERIC-REC.
000490 05 NGEN-CHAR PIC X(30).
000500 05 FILLER PIC X.
000510 05 NGEN-INT PIC -(8)9.
000520 05 FILLER PIC X.
000530 05 NGEN-DATE PIC -(8)9.
000540 05 FILLER PIC X.
000550 05 NGEN-DECIMAL PIC -(6)9.9(2).
000560 05 FILLER PIC X.
000570 05 NGEN-CHAR2 PIC X(30).
000580 05 FILLER PIC X.
000590 05 NGEN-INT2 PIC -(8)9.
000600 05 FILLER PIC X.
000610 05 NGEN-DATE2 PIC -(8)9.
000620 05 FILLER PIC X.
000630 05 NGEN-DECIMAL2 PIC -(6)9.9(2).
000640 05 FILLER PIC X.
000650
000660
000670 WORKING-STORAGE SECTION.
000680 77 WS-PROG-NAME PIC X(15) VALUE "O-GENERIC-TABLE".
000690 77 WS-INSTALATION PIC X(33) VALUE SPACES.
000700 77 A PIC 9(3) VALUE 0.
000710 77 WRITE-CTR PIC 9(7) VALUE 0.
000720
000730
000740 01 TERM-KEY PIC 99.
000750 01 TS PIC 99.
000760 COPY "C:\IINRIS\UNISYS\LIB\WS-DATE.CBL".
000770
000780 01 DISPLAY-TIME.
000790 05 DT-T-HH PIC 99.
000800 05 FILLER PIC X VALUE ":".
000810 05 DT-T-MM PIC 99.
000820 05 FILLER PIC X VALUE ":".
000830 05 DT-T-SS PIC 99.
000840** 05 FILLER PIC X VALUE ".".
000850** 05 DT-T-TT PIC 99.
000860
000870
000880 01 VBOBJ OBJECT REFERENCE CommonVB.
000890 COPY "C:\IINRIS\UNISYS\LIB\CL-WS-FDGEN.CBL".
000900
000910 01 WS-ID.
000920 05 FILLER PIC X(08) VALUE "C:\TEMP\".
000930*= 05 FILLER PIC X(1) VALUE QUOTE.
000940 05 FILLER PIC X(17) VALUE "GENERIC-TABLE.TXT".
000950*= 05 FILLER PIC X(1) VALUE QUOTE.
000960*= 05 FILLER PIC X(1) VALUE ".".
000970
000980
000990 PROCEDURE DIVISION.
001000 START-IT-ALL1.
001010 INVOKE CommonVB "NEW" RETURNING VBOBJ.
001020 COPY "C:\IINRIS\UNISYS\LIB\PR-DISDT.CBL".
001030 COPY "C:\IINRIS\UNISYS\LIB\CL-II-FDGEN.CBL".
001040
001050 ACCEPT WS-TIME FROM TIME.
001060 MOVE WS-T-HH TO DT-T-HH.
001070 MOVE WS-T-MM TO DT-T-MM.
001080 MOVE WS-T-SS TO DT-T-SS.
001090** MOVE WS-T-TT TO WS-T-TT.
001100 DISPLAY "START TIME IS " DISPLAY-TIME.
001110
001120*= OPEN INPUT GENERIC-TABLE.
001130 PERFORM ZGENERIC-TABLE-INPUT.
001140 OPEN OUTPUT NEW-GENERIC-TABLE.
001150
001160 INITIALIZE GEN-KEY.
001170 INITIALIZE GENERIC-REC.
001180*= START GENERIC-TABLE KEY NOT LESS THAN GEN-KEY
001190 PERFORM ZGENERIC-TABLE-S-NOT-LESS
001200 IF GENERIC-TABLE-INV-READ = 1
001210*= INVALID KEY
001220 GO TO PROG-GENERIC-TABLE-END.
001230 READ-GENERIC-TABLE-LOOP.
001240*= READ GENERIC-TABLE NEXT AT END
001250 PERFORM ZGENERIC-TABLE-READ-NEXT
001260 IF GENERIC-TABLE-INV-READ = 1
001270 GO TO PROG-GENERIC-TABLE-END.
001280 ADD 1 TO WRITE-CTR.
001290
001300 MOVE-GENERIC-REC.
001310 INITIALIZE NEW-GENERIC-REC.
001320 INSPECT GENERIC-REC REPLACING ALL "|" BY SPACES.
001330 MOVE ALL "|" TO NEW-GENERIC-REC.
001340 MOVE GEN-CHAR TO NGEN-CHAR.
001350 MOVE GEN-INT TO NGEN-INT.
001360 MOVE GEN-DATE TO NGEN-DATE.
001370 MOVE GEN-DECIMAL TO NGEN-DECIMAL.
001380 MOVE GEN-CHAR2 TO NGEN-CHAR2.
001390 MOVE GEN-INT2 TO NGEN-INT2.
001400 MOVE GEN-DATE2 TO NGEN-DATE2.
001410 MOVE GEN-DECIMAL2 TO NGEN-DECIMAL2.
001420 INSPECT NEW-GENERIC-REC REPLACING ALL "'" BY "`".
001430 WRITE NEW-GENERIC-REC.
001440
001450 GO TO READ-GENERIC-TABLE-LOOP.
001460
001470 COPY "C:\IINRIS\UNISYS\LIB\CL-PR-FDGEN.CBL".
001480
001490 PROG-GENERIC-TABLE-END.
001500*= CLOSE GENERIC-TABLE .
001510 PERFORM ZGENERIC-TABLE-CLOSE.
001520 CLOSE NEW-GENERIC-TABLE .
001530 DISPLAY WRITE-CTR " RECORDS WERE PROCESSED".
001540 ACCEPT WS-TIME FROM TIME.
001550 MOVE WS-T-HH TO DT-T-HH.
001560 MOVE WS-T-MM TO DT-T-MM.
001570 MOVE WS-T-SS TO DT-T-SS.
001580** MOVE WS-T-TT TO WS-T-TT.
001590 DISPLAY " END TIME IS " DISPLAY-TIME.
001600 SET VBOBJ TO NULL
001610 STOP RUN.
001620
--
"I used to write COBOL...now I can do anything."
.
- Follow-Ups:
- Re: SQL access from Cobol Test
- From: tleaders...gmail.com
- Re: SQL access from Cobol Test
- References:
- SQL access from Cobol Test
- From: tleaders...gmail.com
- SQL access from Cobol Test
- Prev by Date: Re: Date Validation in COBOL
- Next by Date: Re: Relevance of languages WAS: Re: Date Validation in COBOL
- Previous by thread: SQL access from Cobol Test
- Next by thread: Re: SQL access from Cobol Test
- Index(es):
Relevant Pages
|
