Syteline Data Archive

1
Syteline Data Archive

• WMPUG Jan 09

2
About Me

• Trendway DBA
• Owner of Big Blue Water in Saugatuck
• 30 years in IT / Manufacturing
• Prior developer (primarily EDI / MRP)
• Progress since 1984
• DBA since 1996
• Syteline, QAD, and Healthmatics
• WMPUG, ACATEC, and OAISD

3
Big Blue Water

• Since 2003
• 4 employees contractors
• Clients throughout West Michigan
• Repair, Networking, Application Support,
  Development, DBA, and Web Design

4
Why you need to archive data

• Get rid of old (or no longer valid) data
• Keep the databases smaller resulting in quicker
  access to the data
• More manageable
• Improve index build and backup/restore time

5
5 Things

• Define the retention schedule
• Identify the data to be archived
• Optionally, copy the data off to a history table
• Delete the data from the production database
• Rebuild indexes or dump/load to tighten things up

6
Our Recommendations

• Off Hours
• IT Department is responsible
• Use background queues when possible
• Monitor the BI file
• Each procedure is documented
• Who
• What
• How
• When
• ETA

7
Infor Month End

• Vendor/Item and Customer/Item PTD and YTD Totals
  to Zero
• A/R Period/Year End Procedure
• A/R Balance History
• End of Period Costing
• Set Item/Warehouse PTD and YTD to Zero
• Post all journals for the period
• Run Financial Statements
• Update Current Period in Accounting Periods
• Fixed Assets Year End Procedure
• Vacation Calculation

8
Period End Data Retention

• EDI Data
• Life of the customer item
• Shipment Detail (x periods)
• Material Transactions (2 yrs required to calc
  prior year cost of purchase)
• Inventory Tag X-Ref to Matl Trans
• Non-Active Inventory tags (also see Job / PO)
• Employee Hours (2 yr)
• Labor Efficiency
• Forecast History (1 yr)

9
Cleanup Transaction Tables

• Matltran
• Jobtran
• Matlearn
• Matlscrp
• Lbrearn
• Lbrscrp
• Mchearn
• Mchscrp
• Delete Material Transactions and Delete Job
  Transactions will cleanup most of these but
  others will remain until Delete End of Period
  Costing Transactions is completed

10
Year End Data Retention

• Customer Orders (5 yr)
• CoHeader Audit Log
• CoLineItem Audit Log
• Packing Slips
• Invoices
• Purchase Orders (5 yr)
• PO Header Audit Log
• Non-Active inventory tags tied to PO
• Job Orders (3 yr)
• Job Transactions
• Non-Active inventory tags tied to a job

11
Gotchas

• If you use transactional replication (AI) to
  maintain a reporting database, these records will
  also be deleted from that database. If thats
  not what you want to do, you might want to
  disable after imaging on the reporting database.
• If you turn off AI during this process, you must
  re-enable it after the process (with a new backup)

12
Archiving Data

• Custom Progress table(s)
• Export to external file
• Dump to external file(s)
• Live Backup

13
Live Backup

• An Archive Backup database is a database that
  has been archived off to an external hard drive,
  along with all code and programs necessary to run
  that database, and then configured so that it
  will run in standalone read-only mode from where
  ever the drive may be plugged in. 
• Two copies of the database are created one for
  onsite storage and the other for offsite
  storage. 
• Once the database has been created, the intent is
  that it will be used for archive purposes only
  and will never have additional data added to it.

DISCLAIMER Contact your Progress Sales Rep to
determine software licensing requirements.
14
The Magic of a Live Backup

• Complete, functional Syteline installation
• Read-Only
• Run Syteline on any machine without Syteline or
  Progress installed on that machine
• 2 Copies one onsite and one offsite
• Use for reporting
• Does not alter the files stored on the local hard
  drive

15
Prepare for a Live Backup

• Determine space requirements
• Progress
• Syteline
• Custom Code
• Full Backup
• Full Dump
• DB Copy
• Get and prepare the disks
• Remove junk
• Format NTFS
• Backup
• Dump Data

16
Moving Files

• Copy Progress, Syteline, Custom Code, and DBs
• Dont forget wordpad and PF / INI files
• Copy the backup files
• Copy the dump files

17
Modify the connection

• Modify ONE PF file to remove all references to
  network drives
• Add 1 user (n1) and Read-Only (-RO) to the PF
  file
• Remove other PF files
• Modify INI to remove network references and point
  to the new DBDIR
• Remove network connect options and references to
  network drives
• Change temp to C
• Modify ST so the DB knows where it is
• prostrct repair dbname

18
Test the connection

• Create an ADMIN icon
• G\Progress\83E\bin\prowin32.exe -pf
  G\symdb\admin.pf -basekey INI -ininame
  G\symdb\admin.ini -p menu/adm-menu.p
• Verify the DBDIR
• Generate list of parameter files (should only
  have ONE)
• ADMIN -gt RUN SYTELINE -gt SINGLE USER (make sure
  it comes up)
• Verify the DBDIR displayed in Syteline is
  accurate
• Verify that DB Administration shows the correct
  db(s)

19
Finish it up

• Change the SYMIX password
• Remove all unused extents
• Create a readme file to explain the contents
• How to use it
• Drive Reference
• Directories and what they contain
• Create a 2nd copy

20
The final test

• Move the drive to a machine where neither
  Progress or Syteline are installed and run it.

21
3481-B Blue Star HwySaugatuck MI 49453(269)
857-5517Betty_at_BigBlueWater.comA copy of this
presentation can be downloaded atwww.bigbluewater
.com
Questions?