Senior CNC Tasks – Executing SQL statements DOs and DONT’S
SQL and CNC
Being a CNC i.e. JD Edwards System Architect, as we would like to call, it is our responsibility to have knowledge about database management system and expertise/experience with SQL databases such as Oracle, SQL Server and DB2/400 with regards to EnterpriseOne.
We will be required to cater to various SQL execution requests ranging from data extraction using DML SELECT queries, right up to creating views and granting access to them using DDL commands. This document will provide knowledge about how to connect to various databases and execute SQL statements.
Getting started with SQL developer
Before we can execute SQL statements, we need to setup a database connection. We will be using SQL developer software for interacting with the databases. Install the latest version from Oracle’s website and get started with it.
Setting up database Connection
Once we have SQL developer ready and opened, we need to setup database connections according to our requirement. In this example, we will be demonstrating how to connect to TESTDTA data source on jde001d database hosted on exadata01 server.
Once you enter the details, test the connection using test button. Once status is shown as success, you can save the details for later use.
Then connect to the data source using Connect button.
This will open a SQL worksheet, as shown below:
Worksheet is a place where we will be writing and executing SQL queries.
To view more details about database objects, we can expand the connection in the left pane and use filters to view more details.
This is useful to check if a particular view, table or index already exists. We can use the Name Like filter to obtain a range of tables starting with, ending with or containing certain characters. Once we find those particular objects we can select them to view more details about them in the right pane.
This comes in handy to obtain SQL for views, columns in a particular index and their order etc.
More details about other connections can be obtained from P986115
Data Source Use -> DB
Different types of SQL statements
Most commonly executed SQL statements are as follows:
DML Commands:
- SELECT
- UPDATE
- DELETE
- INSERT
DDL Commands:
- TRUNCATE
- CREATE
- GRANT
Let us look into each one of them.
SELECT Statements
SELECT statements are requested for extracting data from database. Mostly it is requested by functional users when their requirement of data is complex and cannot be obtained from databrowser application of JDE. Before providing extracts, check if it is achievable using databrowser, if so, advise the requestor to make use of the same.
Update, Insert and Delete Statements
Update, insert and delete statements involves modification of data in tables. In case of update and delete statements, make sure to use to execute SELECT COUNT(*) SQL to confirm the count of rows. If they match with the ones specified by the requestor, only then we can proceed with executing the same.
Some tables have audit fields associated with them. Audit fields helps us to keep a track of update time, program used to update the data and user updating the same. These fields have column names as UPMJ, PID and USER. In case of JDE tables, for each of these columns, we will have a table prefix based on the table.
While executing update statements, make sure to check for audit fields, if they exist, we are supposed to update them accordingly.
UPMJ – Date updated: Must contain the julian date for the day when the SQL is being executed.
USER – User: Must contain the value SQL, as we are updating the rows using SQL.
PID – Program: Must contain the relevant case number, for e.g., CL12345
In a similar manner while executing the insert statements, make sure to check and update the audit fields accordingly.
Truncate Statement
In SQL, the TRUNCATE TABLE statement is a Data Definition Language (DDL) operation that marks the extents of a table for deallocation (empty for reuse). The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms.
This statement can be used when there is request for emptying the table by deleting all records.
Create Statements
Create statements can be used to create a wide range of database objects. Considering the scope of this document, we will be focusing on mainly 2 types of create statements. CREATE TABLE and CREATE VIEW.
CREATE TABLE:
Whenever there is a request for data backup, table generation and restore mentioned in special instructions, we will be required to create a backup table in respective environment using the CREATE TABLE statement. Table backup requests can also be used for fail safe purposes while experimenting in test environments. Depending on the requirement, we will be required to create a backup table for the same. Let us take an example, to create a backup of table F551234 in QA environment, we use the SQL statement as:
CREATE TABLE qadta.f551234_bkp040215 AS SELECT * FROM qadta.f551234;
GRANT Statement
Use the GRANT statement to give privileges to a specific user or role, or to all users, to perform actions on database objects. Considering the scope of this document we will focus on granting privileges to database object such as VIEW. Once a VIEW is created in database, we will be required to grant access to it for it to work in JDE. For granting access to view, say for e.g. FV551234 in QA, we use the following statement:
GRANT ALL ON PUBLIC TO qadta.fv551234;
This will make the view accessible from JDE applications.
Last few words…
Executing incorrect SQLs can cause data integrity issues, data loss, inconsistencies and damage to smooth functioning of the system. Take backup of rows being affected, wherever possible. In case of reverting the changes, use this backup. If there is no backup of data being modified or deleted, and an issue occurs, inform the team. Raise a request with service desk responsible for database management request them to restore the data for respective table(s). Use the tool responsibly, follow the process flow and execute SQLs only when necessary.
