KB_SQL is the data extraction and analysis tool designed to work with M or MUMPS databases. It simplifies the process of defining databases and adding, updating, and deleting data without using a programming language like Java, C#, C++, or Python. While the MUMPS database and M programming language provide an efficient system for handling a large amount of data, many are unfamiliar with its use. That’s why we are providing this concise guide to KBSQL DB conversion. This article will review the steps required to extract, transform, and move data using KB_SQL.
1. Study the Database
The M database has three user groups: the DBA, the SysMgr, and the End User. The DBA has all the privileges to create and modify database tables, views, and stored queries.
The SysMgr user group, on the other hand, does not have the privilege to modify database objects. But users in the SysMgr group may access all tables, views, and queries.
This guide assumes that the person trying to convert data using KB_SQL is in the SysMgr user group.
Load the KB_SQL Editor. From the System Manager menu, select Data Dictionary.
Since the system manager can’t modify the data dictionary, only Table Print and View Print options will appear under the Data Dictionary.
Choose Table Print and leave the Schema name field blank.
Print out all the tables and globals. Globals are the M data structures that feed data into tables.
2. Master the SQL Editor
The SQL Editor is the interface you will interact with most while using KB_SQL. It enables you to do the following:
- Export data to other applications
- Run reports in batches
- Set times for queries to run
- Create report prototypes that contain a few rows
To use the editor effectively, you need to know how to start and exit the interface. Then master how to add, modify, and delete SQL queries and print or save query results.
3. Create Your SELECT Statements
Use SELECT statements to extract data from the database tables. KB_SQL works with standard ANSI SQL so that you can use SELECT statements with various clauses and conditions.
To add a new SELECT query, you must select “insert” from the selection window.
Then provide a new query name or enter the name of a saved query for KB_SQL to copy information.
After that, provide the required query information for proper documentation.
Type your SELECT statement in the text window. For example, to extract all the records of a patient from the patient’s table, type:
SELECT *
FROM patients
Then choose Run from the menu to save and execute the SQL statement.
Click on Print to send the results to a printer.
You may save the SELECT statement by clicking on Save.
4. Export to a Suitable Format
Usually, the screen is the default device for displaying query results. But when you are performing a database conversion, you need to export data to a file.
Use the SET statement for this purpose. Place the statement before your SELECT statement.
To send your query results to a file named Patients.txt on drive D:, type:
SET FILE=’D:\PATIENT.TXT’,FORMAT=’REPORT’
Choose the Quit menu option to save and exit the current query.
5. Load the Data Into a New Database
The method you use to load data into your new database depends on the tools available for that database system. There are data loading tools for moving data from text files into database tables for most popular databases such as SQL Server, MySQL, Oracle, PostgreSQL, and IBM DB2.
Before loading the data, ensure that the data types in the data definition for each field in the table match the kind of data you intend to load into it.
Converting a production KB_SQL database is a more complex task than what we have shared here. And for healthcare data, there are some critical security requirements to meet. For this reason, you need to work with a healthcare data archiving and migration expert.
Get Expert Help for Your KBSQL DB Conversion
Contact MediQuant today at 844.286.8683 to discuss your database migration needs. Visit our contact page today to book a free consultation or see a demo of our archiving solution.