Rob Fitzpatrick
ProgressTalk.com Sponsor
A bit of background: I'm trying to devise a way to get all table record counts quickly, i.e. more quickly than tabanalys. The objective is to be able to produce before-and-after lists of all user tables and their record counts for use in a dump and load, and diff them to ensure that all records that were dumped got loaded to the new DB. There are a few KB articles about different ABL strategies for fast counts, like using queries or using FOR-EACH with FIELDS. I know that ABL is slow for this.
For comparison I played with 'select count(*) from PUB."my-table"' in sqlexp. In a medium-sized table (~ 300,000 records) my first ABL query ran in 11.7 seconds, and subsequent ones were consistently around 4.5 seconds, with either technique. (I guess the first query warmed up the buffer pool?) I didn't time the SQL query, but it ran sub-second; almost instantaneous. All queries were run on the DB server, and the 4GL client was self-service. I don't really understand why the SQL-92 engine is so much faster but for now I decided to just accept that fact, go with the SQL approach, and see where it leads me. I have very little experience with using SQL clients in Progress.
I'm experimenting with writing and running SQL queries from ABL, and then kicking the results to a flat file I can parse later into a format suitable for diffing. The problem is I'm getting errors that I can't find a reason for. Most of my queries return results and they agree with tabanalys output, but every now and again in the output I get a SQL error, like 7519 (Table/View/Synonym not found) or 10713 (Syntax error at or about (statement excerpt)). But it's not as if I'm writing the queries manually; the data is coming from _File.
So here is my code (ugly and incomplete , but more or less functional):
The first time I ran it against a 353-table test DB, it ran almost perfectly. The output looks like this:
I expect it to look like that; I haven't done any parsing logic yet. But at the 352nd table the output file ended with:
So does the "se" refer to the beginning of a select statement? And if so, why just that one? I've been up and down the generated .sql file and can't find an obvious problem. I removed the last two selects and ran it manually and it worked. I retyped the second last one and ran it (352 selects in the file) and it worked. I retyped the last one into the file (353 selects again) and it worked. So I had two files, one that throws an error and one that doesn't, and they're identical. Byte for byte. I don't mind errors, but phantom errors that come and go drive me nuts.
I wondered if random TCP errors could be involved. So I put client and server on different machines and scoped the traffic with tcpdump. But I don't know really what I'm looking at. I can see the select statements going to the server, and the record counts going back to the client, but I don't know the layout of all the data so the errors are hard to find. Is the wire protocol documented somewhere? Would that be part of the SQL standard, or ODBC?
If anyone can point out what I'm doing wrong (apart from making work for myself ) or suggest why I'm seeing these errors, I'd appreciate the help.
For comparison I played with 'select count(*) from PUB."my-table"' in sqlexp. In a medium-sized table (~ 300,000 records) my first ABL query ran in 11.7 seconds, and subsequent ones were consistently around 4.5 seconds, with either technique. (I guess the first query warmed up the buffer pool?) I didn't time the SQL query, but it ran sub-second; almost instantaneous. All queries were run on the DB server, and the 4GL client was self-service. I don't really understand why the SQL-92 engine is so much faster but for now I decided to just accept that fact, go with the SQL approach, and see where it leads me. I have very little experience with using SQL clients in Progress.
I'm experimenting with writing and running SQL queries from ABL, and then kicking the results to a flat file I can parse later into a format suitable for diffing. The problem is I'm getting errors that I can't find a reason for. Most of my queries return results and they agree with tabanalys output, but every now and again in the output I get a SQL error, like 7519 (Table/View/Synonym not found) or 10713 (Syntax error at or about (statement excerpt)). But it's not as if I'm writing the queries manually; the data is coming from _File.
So here is my code (ugly and incomplete , but more or less functional):
Code:
/* Count records in all tables */
def var v-dbname as char no-undo format "x(11)".
def var v-sqlport as integer no-undo format ">>>>9".
def var v-user as char no-undo format "x(16)".
def var v-password as char no-undo format "x(16)".
def var v-outfile as char no-undo format "x(40)".
update v-dbname label "Logical DB name" colon 20 with side-labels.
update v-sqlport label "SQL port number" colon 20 with side-labels.
update v-user label "DB User name" colon 20 with side-labels.
update v-password label "Password" blank colon 20 with side-labels.
v-outfile = "count_" + v-dbname + "_"
+ string( year( today ),"9999" )
+ string( month( today ),"99" )
+ string( day( today ),"99" )
+ "-"
+ replace( string( time, "HH:MM" ), ":", "" ).
def stream out.
output stream out to recordcounts.sql.
put stream out unformatted "set transaction isolation level READ UNCOMMITTED ;" skip.
put stream out unformatted "commit work;" skip.
for each _file no-lock where _file._tbl-type = "T":
put stream out unformatted "select count(*) from PUB." quoter( _file._file-name )";" skip.
end.
put stream out unformatted "commit work;" skip.
output close.
os-command value( "sqlexp -char -db " + v-dbname
+ " -S " + string( v-sqlport )
+ " -infile recordcounts.sql"
+ " -outfile " + v-outfile
+ " -user " + v-user
+ " -password " + v-password ).
The first time I ran it against a 353-table test DB, it ran almost perfectly. The output looks like this:
Code:
count(*)
--------------------
268
count(*)
--------------------
99
count(*)
--------------------
648
<etc.>
I expect it to look like that; I haven't done any parsing logic yet. But at the 352nd table the output file ended with:
Code:
=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-210056
[DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL statement at or about "se" (10713)
So does the "se" refer to the beginning of a select statement? And if so, why just that one? I've been up and down the generated .sql file and can't find an obvious problem. I removed the last two selects and ran it manually and it worked. I retyped the second last one and ran it (352 selects in the file) and it worked. I retyped the last one into the file (353 selects again) and it worked. So I had two files, one that throws an error and one that doesn't, and they're identical. Byte for byte. I don't mind errors, but phantom errors that come and go drive me nuts.
I wondered if random TCP errors could be involved. So I put client and server on different machines and scoped the traffic with tcpdump. But I don't know really what I'm looking at. I can see the select statements going to the server, and the record counts going back to the client, but I don't know the layout of all the data so the errors are hard to find. Is the wire protocol documented somewhere? Would that be part of the SQL standard, or ODBC?
If anyone can point out what I'm doing wrong (apart from making work for myself ) or suggest why I'm seeing these errors, I'd appreciate the help.