save 5 hours a day by integrating rpg to sql server, excel, and other databases

All trademarks and registered trademarks are the property of their respective owners.© HelpSystems LLC. All rights reserved.

Save 5 Hours A Day by Integrating RPG to SQL Server, Excel, and Other DatabasesProvide Real-Time IBM i Access to Remote System Data from RPG

Save 5 Hours A Day By Integrating RPG to SQL Server, Excel, and Other Databases

Your Presenter

Richard SchoenDirector of Document Management Technologies


• HelpSystems overview

• Common use cases for IBM i data integration

• RPG2SQL Integrator overview

• Demo

• Q&A

Agenda


Flexible IT Software Solutions

IT Operations Management

IT InfrastructureMonitoring

Cybersecurity

Document & Forms Management

Business Intelligence

Compliance & Audit Reporting

• Workload Automation & Job Scheduling

• Business Process Automation

• Data Backup Management

• Disk Space Management

• Message & Event Monitoring

• Performance & Application Monitoring

• Remote Monitoring & Management for MSPs

• Network Monitoring

• Vulnerability Assessment

• Virus Protection

• Identification & Access Management

• Security & Integrity Monitoring

• Security Policy Management

• Intrusion Prevention & Detection

• Encryption

• Document & Image Management

• Automated Process & Workflow

Management

• Electronic Forms Management

• Signature Capture & Approval

Management

• Enterprise Data Access

• Executive Dashboards &

Reporting

• Mobile Data Access

• Data Warehousing

• Compliance Reporting

• System & Audit Reporting

• SOX Compliance

• HIPAA Compliance

• PCI Compliance


Document Management Solution Overview


Key Information Lives on Many Platforms

Business Need

Integrate data across platforms

Solution: RPG2SQL Integrator

• Databases across different platforms don’t talk to each other:

– DB2, Microsoft SQL Server, Oracle, MySQL, PostgreSQL, Dbase

• Data comes in from vendors and customers in various formats

– Microsoft Excel, Microsoft Access, CSV Files, Flat Files

• RPG2SQL Integrator brings these platforms together.


Lowering the Cost of Processing Multiplatform Data

Business Need

Utilize multiplatform data with IBM i apps


• Eliminate data replication

• Stop manually merging and re-keying data

• Reduce the risk of data validation mistakes

• Automate the processes used to re-distribute information

• Reduce the need for specialized PC business analysts to create PC formatted output in Excel and other formats

• Eliminate time-consuming manual processes for uploading and reformatting data

• Lower labor costs and streamline data processing and reporting


Manufacturing Use Case

Business Need

Manufacturing businesses run many disparate systems


• Use multiple systems:

– ERP runs on IBM i

– Shop floor data collection runs on AIX system with Oracle database

• Send shop floor orders to the data collection system as they are generated on IBM i

• Return information from data collection system to IBM i

• Get real-time interaction between systems


Reporting and Data Visibility

Business Need

Consolidate and access data from multiple platforms for reporting


• Speed up decision-making with adequate reporting

• Go beyond creating simple text reports is not enough

• Give management and power users data in more complex formats

• Utilize IBM i-focused skills of an internal team

• Utilize existing development team resources to generate high-quality complex reporting

• Take advantage of an existing data access tool for reporting


Grocery or Retail Store Use Scenario

Business Need

Consolidate and report on retail point-of-sale data

Solution: RPG2SQL


– Accounting systems runs on IBM i

– Point of Sale (POS) system runs on Windows Server and uses SQL Server database

• Move transactions to IBM i at end-of-day

• Merge transaction data into the accounting system

• Write IBM i reports to perform daily reporting

• Leverage existing core IBM i development staff to build reporting


Property Management Use Case Scenario

Business Need

Simplify complex government tax reporting



– Accounting system runs on IBM i

– Complex reporting spreadsheet template is received from state tax authority

• Meet requirements to report data back using state-supplied template

– Template contains multiple sheets and protected cell areas

• Accumulate data for an entire year and report back to state

• Stop manually re-keying information from IBM i into the spreadsheet template each month-end

• Automate the entire spreadsheet generation and reporting process


Casino Executive Reporting Use Case Scenario

Business Need

Give the executives what they need to manage effectively



– Accounting and casino systems run on IBM i

• Fulfill management’s need for multi-tab complex spreadsheets for month-end and periodic reporting

– Spreadsheets can’t be simply generated by an SQL query—existing RPG business logic was involved

• Accumulate data in the spreadsheets across multiple tabs

• Automate the entire spreadsheet generation and reporting process

• Generate regular management spreadsheets using existing RPG development staff


Hospital Use Case Scenario

Business Need

Provide one view of patient data across systems



– Hospital software and financials run on IBM i

– Several other systems run on different database platforms

• Interchange patient data between systems automatically

• Show data from one central IBM i application

• Utilize existing RPG development skill sets


What is RPG2SQL Integrator?

• Use RPG and COBOL programs to read and write to any database

• Support any ADO, OLEDB, or ODBC data source

• Interact directly with Microsoft Excel spreadsheets

• Support Excel formatting

• Insert, update, and delete records

• Run SQL-stored procedures

• Do selective data copying or replication

• Use Excel import and export commands for RPG and COBOL


Real-Time Access to SQL Server and Other Databases

Business Need

Interactive and batch program access to any database


• RPG or COBOL applications use any database with an ODBC driver

• Embedded service program calls to existing RPG programs

• Sub-files, interactive, and batch mode applications

• Sub-second response time


Read and Write Excel Files

Business Need

Interactive and batch program access to Excel files


• RPG or COBOL applications create and use Excel spreadsheets

• Embedded service program calls to existing RPG programs

• Sub-files, interactive, and batch mode applications


Export from IBM i Files to Excel

Business Need

Quickly export IBM i data files to Excel


• Use Excel Export Command: SQLXLSEXP

• Select any physical file to export

• Select record range to export

• Select field column range to export

• Export field names as column headings

• Append or replace Excel output file

• Select worksheet name


Excel Export Command Example

SQLXLSEXP INFILE(QIWS/QCUSTRPGSQ)

OUTXLS('c:\rjstemp\qcustcdt.xlsx')

OUTSHEET(*ACTIVE *REPLACE)

HEADER(*COLHDG)

LOGMSG(*YES)

HOST(*CURRENT)

RCDFIRST(*FIRST)

RCDLAST(*LAST)

FLDFIRST(*FIRST)

FLDLAST(*LAST)


Import Excel Data to IBM i Files

Business Need

Quickly import to IBM i data files from Excel


• Use Excel Import Command: SQLXLSIMP

• Select spreadsheet to import

• Match spreadsheet column names to PF (required)

• Use first row of Excel as column names

• Append or replace file data on import

• Select worksheet name

• Skip blank rows


Excel Import Command Example

SQLXLSIMP INXLS('c:\rjstemp\qcustcdt.xlsx')

INSHEET(*ACTIVE)

OUTFILE(QIWS/QCUSTRPGSQ)

OUTMBR(*FIRST *REPLACE)

HDRROWS(1)

LOGMSG(*YES)

HOST(*CURRENT)

BLKROW(*SKIP)


System Requirements

IBM i Server

RPG Program

RPG2SQL Service Program

PC Server Component


System Requirements

• IBM i library: V5R4 and above

• RPG2SQL service programs and sample code

• PC server component: Windows 7, 10, Server 2008 and 2012

• At least one PC server instance

• Appropriate database ADO, ODBC, or OLEDB drivers on PC server

• Microsoft Excel loaded on server (if interacting with Excel workbooks)

• ILE RPG Application bound to RPG2SQL Service Program

UP NEXT...

Demo Time

UP NEXT...

Questions and AnswersPlease enter your questions in the chat log


Thank You for Attending

For More Information:

Website:

http://www.helpsystems.com/rjs

Telephone:

Sales: 800-328-1000Support: +1 952-933-0609

Technical Experts:

[email protected]@helpsystems.com

mailto:[email protected]

mailto:[email protected]