I ask this without much hope of an easy answer.

Does anyone know if there is a quick way to get a row count of all tables in a Baan company. We are migrating from Triton 3.1a / Baanbase to Baan IVc4 / Oracle. In one of our test migrations (using bdbpre and post) we found that several tables did not cross with all rows.

What I am looking for is some sort of before and after snapshot to help verify the data, without opening up each and every table in GTM to count the rows.

Thanks,
Rob

does the session ttaad4222m000 count number of records by table exist in triton 3.1a. If so that will do the job.

bdbpre as well as bdbpost will give you a count of processed records for each table.
Thus comparing the logfiles should do the trick.
Another method is to dump the table once again after conversion and simply count the rows with wc -l

hth

Norbert

If wish to get this in 4gl program , u can use db.nr.rows function . Otherwise ttaad4222m000 is the best way to get the record count .

Hi Rob,

This is a bit of a late response but....

We ran into the same question here and came up with the script below. It gives you the database name, table name and # of rows for each qualifying table. You can narrow it down using company number (as we did) with the "tabname" field.

This was done for Informix, but should be a easy conversion for Oracle. I hope this helps....


DATABASE sysmaster;

SELECT
systabnames.dbsname,
systabnames.tabname,
systabinfo.ti_nrows
FROM
systabinfo, systabnames
WHERE
systabinfo.ti_partnum = systabnames.partnum
and
systabnames.dbsname = "baandb"
and
systabnames.tabname like "%001"
and
systabinfo.ti_nrows > 0
ORDER BY
systabinfo.ti_nrows desc

Actually, this can not be converted to oracle so easily, as the corresponding views simply do not exists or rely on statistics, which might simply be wrong.

But otherwise nice.

In Oracle, and for BaanIV (and V?) you can use the following commands:

-- replace XXX below by your company number
-- (google for the commands you do not know)
declare
nrrows integer;
begin
for i in (
select table_name from all_all_tables where table_name like '%XXX'
) loop
execute immediate 'select count(*) from '||i.table_name into nrrows;
dbms_output.put_line(lpad(i.table_name,12)||': '||nrrows||' row(s)');
end loop;
end;

[Above works using Toad 9.0.1 against Oracle 9.2.0.6, you may need to adapt for your Oracle version or to be run thru sqlplus, must be run as the schema owner or with select rights into the affected tables]

Hi Victor,

thanks for the script, I was looking for something like this for some time!
There were some minor spelling mistakes in there to get it run. So here a slightly corrected version:


set serveroutput on SIZE 1000000

declare
nrrows integer;
begin
for i in ( select table_name from all_tables where table_name like '%100' )
loop
execute immediate 'select count(*) from '||i.table_name into nrrows;
dbms_output.put_line(lpad(i.table_name,12)||': '||nrrows);
end loop;
end;
/


Note 1: The "set serveroutput ..." is required if run from sqlplus
Note 2: You have to execute the script as the shema user (ie baan)
Note 3: Above example is for company 100
Note 4: The "/" will start the PL/SQL Block