Record Counts - for Data Conversion

DBohner-(db)

Legendary Poster
Howdy,

I'm trying to figure out a simplified way to do Record Counts - to Proof Data Migrations.

I've read Craig's post at: Record Count Function
and I do understand the concept.

However, my skillset is not heads-down C.

Ideally, I would have a table that:
- Reads the Table Name
- Pass in the From Data Source
- Get back the Record Count for From Data Source
- Pass in the To Data Source
- Get back the Record Count for To Data Source

Sounds simple enough, just looking for an E1 Example to learn the new logic.

Building that syntax around the "JDB_SelectKeyedGetCount" API for the above logic - hasn't proved to be as straight forward as I'd hoped.

Thoughts?

(db)
 
Stupid me, I should have consulted the Knowledge Gungle, first.


E1: RDA: How to Count the Number of Records in a Table Dynamically for Multiple Tables (Doc ID 974172.1)

Call BSFN B9600710 - GetTableRecordCount (Get Table Record Count) as below,
a. IN: szSourceEnvironment (ENHV)
b. IN: szTargetEnvironment (ENHV)
c. IN: szTableName (TBLE)
d. OUT: mnSourceTableCount (CNT)
e. OUT: mnTargetTableCount (CNT)
f. IN: szUserGroup UGRP)

At a glance - looks like it does everything I desire......

Gophigure?

(db)
 
I needed the same thing. Created a grid with table name, # records, with the source as a lookup on the form:

Get Table Record Count based on Data Source (B9800200)
...GC Table Name -> BF szTableName
...FC DatabasePath_DATP <> BF szDataSourceName
...GC Record Count <- BF mnCount

Worked well and very easy.
 
OK - I've completed a successful Count of all records in both data sources... here's the steps:

Create a table with Object name, From ENV and To ENV
- F55THIS
+ OBNM
+ OMWFDS
+ OMWTDS
- All Three are Primary Keys

Create a BSVW over the table (Include all columns)
- V55THIS

Create an applications (Headerless Detail)
- Over V55THIS
- Add Find Button
- Add Delete Button
- Turn on Multi-Select
- Turn on QBE

Now a magic SQL Statement to pull ALL the Files Names from any particular environment:
- On SQL Server, Something similar to:
+ select TABLE_NAME
from information_schema.TABLES
where TABLE_SCHEMA = 'TESTDTA'
order by TABLE_NAME
- Every Database has it's own means for querying the table names from a schema - you may have to ask for help from your DBA. The list should be at least 3500 tables from Business Data

Dump the Table Names into EXCEL and add two more columns
- From Data Source
- To Data Source

Populate the new columns with the correct Data Source Names (not to be confused with ENV Names.

Now, open the application and dump the Excel Table into the grid. You may get duplicates - the OBNM is limited to 10 characters, and your Developers / DBAs may have made backups of the original E1 tables, using dates (F0911_20111207).... You may want to cleanse backups from the Spreadsheet before play time.

OK the application and save all the data to the F55THIS table.

Create a new UBE
- use the V55THIS Business View
- use the following ER in the code (should explain itself???

Listing of ER for Report: Report : Conversion Validation - Final Processor Count (R55THIS)

***********************************************************************
GLOBALS: Variables (Global)
***********************************************************************
rpt_szDataSource
rpt_idRecordSet
rpt_szTableName
rpt_idCount

=======================================================================
SECTION: Conversion Validation - Final Processor Count [GROUP SECTION] ()
=======================================================================
OBJECT: SECTION
EVENT: Do Section
-----------------------------------------------------------------------
evt_ErrorYN
OPT: Using Defaults
0001 VA rpt_szTableName = BC Object Name (F55CONVS)(OBNM)
0002 VA rpt_szDataSource = BC OMW From Data Source (F55CONVS)(OMWFDS)
0003 VA evt_ErrorYN = "N"
0004 Init Selection (B9600430 - Init Selection)
VA rpt_szDataSource -> BF szDataSource
VA rpt_idRecordSet <- BF idRecordSet
VA rpt_szTableName -> BF szTableName
0005 If SV Error_Status is equal to CO SUCCESS
0006 Get Record Count (B9600430 - Get Record Count)
VA rpt_idRecordSet -> BF idRecordSet
VA rpt_idCount <- BF idCount
0007 VA evt_ErrorYN = "N"
0008 End If
0009 Clean Up (B9600430 - Clean Up)
VA rpt_idRecordSet <> BF idRecordSet
0010 VA rpt_idRecordSet = ""
0011 RV Object Name = VA rpt_szTableName
0012 RV idRecordCountFrom = VA rpt_idCount
0013 VA rpt_szDataSource = BC OMW To Data Source (F55CONVS)(OMWTDS)
0014 Init Selection
VA rpt_szDataSource -> BF szDataSource
VA rpt_idRecordSet <- BF idRecordSet
VA rpt_szTableName -> BF szTableName
0015 If SV Error_Status is equal to CO SUCCESS
0016 Get Record Count
VA rpt_idRecordSet -> BF idRecordSet
VA rpt_idCount <- BF idCount
0017 VA evt_ErrorYN = "N"
0018 End If
0019 Clean Up
VA rpt_idRecordSet <> BF idRecordSet
0020 VA rpt_idRecordSet = ""
0021 RV idRecordCountTo = VA rpt_idCount
0022 RV From Data Source = BC OMW From Data Source (F55CONVS)(OMWFDS)
0023 RV To Data Source = BC OMW To Data Source (F55CONVS)(OMWTDS)
0024 RV idRecordCountDifference = abs(([RV idRecordCountFrom]-[RV idRecordCountTo]))
0025 If RV idRecordCountDifference is equal to <Zero> And VA evt_ErrorYN is equal to "N"
0026 Hide Object(RC Review)
0027 Hide Section
0028 Else
0029 Show Object(RC Review)
0030 Show Section
0031 End If

Now, when you run the report (locally only???) - you will get a listing of all business data tables that have mis-matched record counts.

Enjoy!

(db)
 
Could you not cheat and create a dummy table in the One World database that's just an SQL statement?

Create the table in JDE, Drop it on the database then
create view table count as
select count
from xxx

grant access to everyone and then fetch the table in JDE?
 
Hi John,

I had thought of that, creating SQL Views that contained the Record Counts of every table.

Basically, use the System.Tables as the Primary and do a Count(*) on every table there in.

Something I would have to play with - on my own dime, in the future
grin.gif


If someone has already completed that type of task - feel free to share the SQL / View (Virtual Table) logic.

(db)
 
Not the virtual table path taken, but close enough:

CREATE VIEW [dbo].[vTablesWRecords_E1DV]
AS
SELECT 'JDE_DEVELOPMENT' E1DB, u.name SchName, right(u.name,3) TblOwn, o.name TblName, i.rows Rex
FROM JDE_DEVELOPMENT.dbo.sysobjects o
INNER JOIN JDE_DEVELOPMENT.dbo.sysindexes i
ON (o.id = i.id)
INNER JOIN JDE_DEVELOPMENT.dbo.sysusers u
ON (o.uid = u.uid)
WHERE type = 'u'
AND i.indid < 2
AND u.uid > 1
AND i.rows > 0

GO

do the same for PD, then use this cross reference view:

CREATE VIEW [dbo].[vTablesInPDandInDV]
AS
SELECT vTablesWRecords_E1PD.E1DB PRODB, vTablesWRecords_E1PD.SchName PRODOWN, vTablesWRecords_E1PD.TblOwn PDO,
vTablesWRecords_E1PD.TblName PRODTAB, vTablesWRecords_E1PD.Rex PRODREX,
vTablesWRecords_E1DV.E1DB DEVB, vTablesWRecords_E1DV.SchName DEVOWN, vTablesWRecords_E1DV.TblOwn DVO,
vTablesWRecords_E1DV.TblName DEVTAB, vTablesWRecords_E1DV.Rex DEVREX
FROM vTablesWRecords_E1PD LEFT OUTER JOIN
vTablesWRecords_E1DV ON vTablesWRecords_E1PD.TblOwn = vTablesWRecords_E1DV.TblOwn AND
vTablesWRecords_E1PD.TblName = vTablesWRecords_E1DV.TblName
WHERE (vTablesWRecords_E1DV.E1DB IS NOT NULL)

GO
 
Guys, do you think this might be over-kill?

Whenever I go thru data conversions, I just manually run SQL record counts on the top tables as a spot check. That along with running all integrity reports, GL, AP, etc reports has been enough. Here are the typical tables I always check:

SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F03B11]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F03B13]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F03B14]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F0411]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F0413]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F0414]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F0006]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F0901]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F0902]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F0911]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F4301]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F4311]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F43121]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F4311T]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F43199]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F4801]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F1501B]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F15011B]
SELECT COUNT(*)
FROM [JDE_PY].[TESTDTA].[F1502B]
 
Back
Top