SQL Stored procedure to sync identity column in IBM i DB2 (AS400)

Показать описание

Apply and get any of the following Credit cards:

Рекомендации по теме

Комментарии

The CASE statement could be replaced with COALESCE(MAX(IDNCOLNAM), 0)

glenngundermann

You use TRIM for LIBRARY and TABLE in the first SQLSTMT but not in the second statement. Since they are VARCHAR, the TRIM isn't needed. it's not good to do unnecessary steps and it's best to be consistent.

glenngundermann

Why did you put quotes " around the column name, library name, and table name when you built SQLSTMT?

glenngundermann

1. Are you sure you have to declare SQLCODE? Is this not automatically declared under the covers, along with a lot of other variables, like SQLSTATE, etc.?
2. Setting MAXIDNVAL = 0 and SQLSTMT = ' ' are redundant. They will be replaced by the statements following.
3. Why are you using a cursor to execute one single statement only once? Cursors are great to retrieve data, one row at a time, from a result set.

glenngundermann

Hi,

How to uniquely identify a record in AS400 database

Detailed Question:
I have AS400 database and some tables within it.
Tables don't have primary key / unique key / composite key defined and it has duplicate records.

Like oracle has concept of ROWID, in same way do we have something for AS400 database. or can we create something like ROWID?

pavang

Is there anything you can help me with ADMIN stuff in AS400??

joelburner

Have you tried using VALUES to avoid using a cursor? Much less SQL code and I believe better performance.

SET SQLSTMT = 'VALUES (SELECT .... ) INTO ?';
PREPARE STMT FROM SQLSTMT;
EXECUTE STMT USING MAXIDNVAL;

Some references:

glenngundermann

SQL Stored procedure to sync identity column in IBM i DB2 (AS400)

Synchronizing 2 or More PLCs, and Mapping to Stored Procedures

Advanced SQL Tutorial | Stored Procedures + Use Cases

SQL Stored Procedures - What They Are, Best Practices, Security, and More...

SQL Stored Procedures | CREATE PROCEDURE Statement| SQL Server Tutorial for Beginners

How to Create and Use Stored Procedures with Parameters in SQL Server |SQL Tutorial Day 28

MySQL: STORED PROCEDURES

SQL Stored Procedures: The TRUTH in 5 Minutes

Display Read Any Stored Procedure Code In SQL Server sp_helptext

Stored Procedures in MySQL | Advanced MySQL Series

Procedure Tutorial in SQL | SQL Stored Procedure | Procedure in SQL

Tableau and Custom SQL - plus stored procedure, parameters and initial SQL | sqlbelle

Introduction to SQL - Stored Procedure in IBM i (AS400) | yusy4code

Power Automate 101 | SQL Server Actions

Calling a Stored Procedure using Incremental Refresh in Power BI? We think so!

Get Data from Stored Procedure in Power BI Tutorial (34/50)

Follow this SQL RoadMap to kick start your SQL journey | #learncoding

SQL Server Interview Questions and Answers :- What to prepare ?

SQL SERVER 2017 TUTORIAL: PART 05 (STORED PROCEDURE)

SQL Stored Procedure Supported In Power Apps

Tutorial 9- MYSQL Stored Procedures -MySQL Complete Playlist

SQL Server : Read Windows Registry using the extended Stored Procedure: xp_regread. #sqlShorts

Stored Procedure, Function and Cursor| SQL Intermediate Course | Logic First Tamil

Call a SQL Server Stored Procedure using Excel Parameters

Advanced Database Stored Procedures with IF-ELSE Condition (SQL Server)