Filtering Null Values of Type DATS using SAP Open SQL

SAP logo

In October, I started a new phase in my career of twenty-five years as an IT professional: SAP software engineer.

I had worked with SAP in some capacity over the years, but those experiences mostly revolved around connecting APIs to SAP endpoints or querying the SAP database directly using a SQL client like Oracle Developer. I had wanted to learn the inner workings of SAP for many years, but never really had the opportunity until now to devote time to it. I did take a beginning ABAP Objects class back around 2006, but never was in a position to use it.

In my new position, I am learning SAP and its programming language, ABAP, and its implementation of SQL: Open SQL.

If you are familiar with COBOL, the ABAP programming language may not look all that foreign to you. There are plenty of resources that you can use to learn ABAP, even if you don’t have access to an instance of SAP. Even so, I would say that the learning curve when beginning is steeper than it is with more modern programming languages like Python or Ruby.

Open SQL code is embedded quite naturally into the ABAP code, and uses syntax that is familiar to anyone who has used SQL before – for the most part. There are some key differences, and one I’m working on today has to do with null values in the DATS data type, which is for dates. The data type is structured like CHAR(8).

In a standard SQL query, you could filter out null values like this:

SELECT *
FROM ztable INTO #tmp_ztable
WHERE some_date_column IS NOT NULL;

In Open SQL, this does not work. A similar query in Open SQL might look like this:

SELECT *
FROM ztable INTO @DATA(lt_ztable)
WHERE some_date_column NE 00000000.

(The “NE” operator is for “not equal to” and is interchangeable with “<>”.)

Notice that there are no single quotes around the eight zeros in the WHERE clause – it is not a string of eight zeros, which would be represented by ‘00000000’.

Why does SAP do this? I don’t know. I’m sure there’s a reason that I’ll find out at some point, but I haven’t found it yet. There’s much more to learn!

Finding Column and Table Names in Amazon Redshift using SQL

Amazon Redshift logo

Finding metadata regarding table and column names in Redshift can be daunting, especially if you do not know if the tables are internal or external. Redshift has a table called SVV_EXTERNAL_COLUMNS that is useful for looking at external tables, and there are scripts to look at internal tables as well.

The SQL code I wrote below combines these and should make it easier to find what you’re looking for.

select * from (
select t.table_schema as schemaname,
       t.table_name as tablename,
       c.column_name as columnname,
       t.table_type
from information_schema.tables t
inner join information_schema.columns c 
           on c.table_name = t.table_name 
           and c.table_schema = t.table_schema
where t.table_schema not in ('information_schema', 'pg_catalog')      
union all
select schemaname,
       tablename,
       columnname,
       'EXTERNAL TABLE' as table_type
from svv_external_columns)
where columnname ilike '%whatever column%'
and tablename ilike '%whatever table%'
and schemaname ilike '%whatever schema%'
order by schemaname, tablename, columnname;

I have found this to be useful on numerous occasions, especially since the Databaser Explorer in SQL Workbench/J does not yet seem to have a way to search for column names.

Testing Connectivity to Multiple SQL Server Linked Servers

SQL Server 2022

As we are preparing to migrate some of our older SQL Server databases that are nearing their end of support to SQL Server 2019, one of the necessary tasks is determining what Linked Servers need to be established on the new servers to maintain the existing functionality for all of our users’ applications.

Testing the functionality of an individual Linked Server is simple enough – the built-in stored procedure sp_testlinkedserver will return a zero (0) if the link is good, and returns an exception if the link is not.

There is a stored procedure for listing all Linked Servers, but it doesn’t return the health of the link: sp_linkedservers.

How can these be combined to return only the Linked Servers that are currently up?

The solution I developed uses sp_linkedservers to populate a table variable, which is then used in a cursor. For each row in the table, using the sp_testlinkedserver stored proc, an active column is set to 1 if the server is up, and 0 if it is not.

DECLARE @LinkedServerName NVARCHAR(128), @Active BIT, @Message NVARCHAR(MAX)=''

DECLARE @Servers TABLE
(SRV_NAME NVARCHAR(128)
,SRV_PROVIDERNAME	NVARCHAR(128)
,SRV_PRODUCT	NVARCHAR(128)
,SRV_DATASOURCE	NVARCHAR(4000)
,SRV_PROVIDERSTRING	NVARCHAR(4000)
,SRV_LOCATION	NVARCHAR(4000)
,SRV_CAT	NVARCHAR(128)
,Active BIT DEFAULT 1)

INSERT INTO @Servers (SRV_NAME,SRV_PROVIDERNAME,SRV_PRODUCT,SRV_DATASOURCE,SRV_PROVIDERSTRING,SRV_LOCATION,SRV_CAT)
EXEC sp_linkedservers 

DECLARE servercursor CURSOR FOR 
	SELECT SRV_NAME, Active 
	FROM @Servers
	ORDER BY SRV_NAME

OPEN servercursor

FETCH NEXT FROM servercursor INTO @LinkedServerName, @Active
WHILE @@FETCH_STATUS=0
BEGIN
	DECLARE @isUp BIT = 0
		
	BEGIN TRY
		DECLARE @test INT
		EXEC @test = sp_testlinkedserver @LinkedServerName;
		IF @test = 0
			BEGIN
				SET @isUp = 1
			END
		ELSE
			BEGIN
				SET @isUp = 0
			END
	END TRY
	BEGIN CATCH			
		SET @isUp = 0;
	END CATCH
		
	IF @Active<>@isUp
	BEGIN
		UPDATE @Servers 
		SET Active=@isUp
		WHERE SRV_NAME=@LinkedServerName

		SET @Message = @Message + 'Active bit on server ' + @LinkedServerName + ' was set to ' + CONVERT(VARCHAR,@isUp) + '.' + CHAR(10) + CHAR(13)
	END		
	FETCH NEXT FROM servercursor INTO @LinkedServerName, @Active
END

CLOSE servercursor
DEALLOCATE servercursor

SELECT SRV_NAME
FROM @Servers
WHERE Active=1
ORDER BY SRV_NAME

PRINT @Message

Running this code will return an alphabetically ordered list of active linked servers.