DB2 SQL with UR

https://www.toadworld.com/platforms/ibmdb2/w/wiki/6660.uncommitted-read-ur

below is from above link

Uncommitted Read (UR)

UR means that a program can read data that has not yet been committed by another program. No locks are taken except for the special S mass delete lock used to serialize UR readers and mass deleters, which requires an X mass delete lock. In addition, an IX lock on any tablespace in the work file database (DSNDB07, usually) is taken to avoid a drop of the tablespace that is being processed. UR, like all of the isolation levels, causes SQL to be serialized with most utilities, depending on the phase of processing, by using claim and drain processing.

If using UR, your program can be reading data that is in the process of being changed. It provides a high level of concurrency. However, do not use UR if seeing uncommited data could affect a business decision.

Control space bar

Type db.

Then control space bar

DB2 SQL needs less than 80 characters for mainframe FTP whatever

'This routine fix the input file which just SQL for DB2 and split them into less than 80 characters per 'line

Imports System.IO
Module Module1

    Sub Main()
        'Dim inputfile As String = "c:\aurorasql.txt"
        'Dim outputfile As String = "c:\out.txt"
        Dim inputfile As String = "c:\printer.txt"
        Dim outputfile As String = "c:\outprinter.txt"

        ' split SQL statement into less than length 80
        ' first separate by ","
        ' the first line has SQL command "insert" and it is default lenght is less than 80 so no need of comma
        ' others than need comma

        Using sw As StreamWriter = New StreamWriter(outputfile, True)

            For Each line As String In File.ReadLines(inputfile)
                'Console.WriteLine(line)
                Dim len = 80
                Dim arr = line.Split(",")
                For Each Str As String In arr
                    If Str.Length > 80 Then
                        sw.Write(",")
                        Dim arr1 = Str.Split(" ")
                        Dim str2 As String = String.Empty
                        For Each str1 As String In arr1
                            str2 = str2 & str1 & " "
                            If str2.Length > 70 Then 'this is just make sure total is less than 80
                                sw.WriteLine(str2)
                                str2 = String.Empty
                            End If
                        Next

                        sw.WriteLine(str2)
                        str2 = String.Empty
                    Else
                        If Str.Contains("INSERT") Then
                            sw.WriteLine(Str)
                        Else
                            sw.WriteLine(" ," & Str)
                        End If
                    End If
                Next

            Next
        End Using
        Console.WriteLine("done")
    End Sub

End Module

DB2 SQL select CASE

select CASE when max(id) is  null then 1 else max(id) + 1 end from  mytable

Add two counts in SQL

use COALESCE to default to zero:
SELECT COALESCE(MAR1.GENERATED,0)

SELECT  SUM(s.TOTAL_DOCS) totalHours
FROM (
        ( SELECT count(*) as TOTAL_DOCS
  FROM  x WHERE  ..
        )
          UNION ALL

(SELECT count(*) as TOTAL_DOCS FROM  x
          WHERE ...
          )
        ) s

SQL wild card

https://ask.sqlservercentral.com/questions/2903/use-like-where-matching-pattern-is-a-column-value.html
https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#safe=active&q=sql+server+wildcard+on+joined+column