Articles & Content

StackOverflow

StackOverflow is a collaborative programming Q&A community venture

Note: The content of this blog is the opinion and thoughts of the blogger and does not necessarily represent the opinions of IDUG.

 

 
Host Integration server 2016 and BizTalk Arabic text support

I am using BizTalk server 2016 and Host integration server 2016 to connect to DB2 and retrieve the data

Everything is working fine except when text in DB2 is arabic biztalk is receiving ??? in data

i did try this https://uatif.wordpress.com/2009/05/26/ibm-arabic-language-problem-host-integration-server/ but no use

Please respond its urgent issue

How to convert VARCHAR to TIMESTAMP in DB2

I have a varchar value in BD2 Table like below format

121226145503+0530

I want to convert this varchar value to time stamp format like below

2012/12/26 14:55:03
'Dts' is not declared - VB 2010 Connection string as variable to DB2 SSIS package

Full disclosure, I'm in a situation where I'm having to learn VB scripts on the fly. Here is the issue: In VB 2010 Script Task, I have had to hard code a DB2 connection string in multiple script tasks in my SSIS packages. In VB 2005, the connection string was set as a variable, which I have done for VB 2010. I have declared the variable in the script task as read/write. My other variables work, but both my DB2 and MS SQL connections strings are requiring hard coding in order to work. The error I am seeing is "'Dts' is not declared." I am certain it is declared as other parts of the code use dts variables that work correctly. I have scoured for a solution and have yet to find anything that works. Thanks for any insight that can be provided. Code below with sensitive information scrubbed and replaced with asterisks:

' Microsoft SQL Server Integration Services Script Task' Write scripts using Microsoft Visual Basic' The ScriptMain class is the entry point of the Script Task.Imports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports System.Data.OleDbImports System.Data.SqlClient _ _Partial Public Class ScriptMain Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase Enum ScriptResults Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure End Enum ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' ' To open Code and Text Editor Help, press F1. ' To open Object Browser, press Ctrl+Alt+J. Dim db2Con As OleDbConnection = Nothing Dim strError As String = Nothing Dim db2Cmd As OleDbCommand = Nothing Dim db2Rdr As OleDbDataReader = Nothing Dim EODC_Audit_DataTable As New DataTable Public Sub Main() ' ' Add your code here ' Dts.Variables("Audit_Row_Ct").Value = CheckForEODC() Dts.TaskResult = ScriptResults.Success End Sub Public Function CheckForEODC() As Integer CheckForEODC = 0 Try If Not DB2RS.SetDBCon(db2Con, strError) Then Throw New Exception(strError) If Not DB2RS.SetDBCmd(db2Con, db2Cmd, "SELECT * FROM " + Dts.Variables("Region").Value.ToString + ".****", strError) Then Throw New Exception(strError) db2Rdr = db2Cmd.ExecuteReader EODC_Audit_DataTable.Load(db2Rdr) Catch ex As Exception Dts.VariableDispenser.LockForWrite("stdError") Dts.Variables("strError").Value = "Load Error: " & ex.Message & "
 " Dts.Variables.Unlock() Finally DB2RS.DisposeOfObjects(db2Con, db2Cmd, db2Rdr) End Try If (**** < 1) Then CheckForEODC = 0 Else CheckForEODC = **** End If End FunctionEnd ClassPublic Class DB2RS Public Shared Function SetDBCon( _ ByRef dbCon As OleDbConnection, _ ByRef strError As String _ ) As Boolean Try dbCon = New OleDbConnection(Dts.Variables("DB2ConnectionString").Value.ToString) dbCon.Open() SetDBCon = True Catch ex As Exception strError = ex.Message SetDBCon = False End Try End Function Public Shared Function SetDBCmd( _ ByRef dbCon As OleDbConnection, _ ByRef dbCmd As OleDbCommand, _ ByVal strCmd As String, _ ByRef strError As String _ ) As Boolean Try dbCmd = New OleDbCommand(strCmd, dbCon) dbCmd.CommandType = Data.CommandType.Text SetDBCmd = True Catch ex As Exception strError = ex.Message SetDBCmd = False End Try End Function Public Shared Sub DisposeOfObjects( _ Optional ByRef dbCon As OleDbConnection = Nothing, _ Optional ByRef dbCmd As OleDbCommand = Nothing, _ Optional ByRef dbRdr As OleDbDataReader = Nothing, _ Optional ByRef dbAda As OleDbDataAdapter = Nothing, _ Optional ByRef dbDSet As DataSet = Nothing, _ Optional ByRef dTable As DataTable = Nothing, _ Optional ByRef dView As DataView = Nothing _ ) Try If Not IsNothing(dbDSet) Then dbDSet.Dispose() dbDSet = Nothing End If Catch ex As Exception dbDSet = Nothing End Try Try If Not IsNothing(dbAda) Then dbAda.Dispose() dbAda = Nothing End If Catch ex As Exception dbAda = Nothing End Try Try If Not IsNothing(dbRdr) Then If Not dbRdr.IsClosed Then dbRdr.Close() dbRdr = Nothing End If Catch ex As Exception dbRdr = Nothing End Try Try If Not IsNothing(dbCmd) Then dbCmd.Dispose() End If Catch ex As Exception dbCmd = Nothing End Try Try If Not IsNothing(dbCon) Then If dbCon.State = ConnectionState.Open Then dbCon.Close() End If dbCon.Dispose() End If Catch ex As Exception dbCon = Nothing End Try Try If Not IsNothing(dTable) Then dTable.Dispose() dTable = Nothing End If Catch ex As Exception dTable = Nothing End Try Try If Not IsNothing(dView) Then dView.Dispose() dView = Nothing End If Catch ex As Exception dView = Nothing End Try End Sub Public Shared Function Null2Space( _ ByVal strValue As Object, _ Optional ByVal blnDash As Boolean = False _ ) As String Try If IsNothing(strValue) Then If blnDash Then Null2Space = " - " Else Null2Space = " " End If ElseIf strValue Is System.DBNull.Value Then If blnDash Then Null2Space = " - " Else Null2Space = " " End If ElseIf strValue.ToString.Trim.Length() < 1 Then If blnDash Then Null2Space = " - " Else Null2Space = " " End If Else Null2Space = strValue.ToString.Trim() End If Catch ex As Exception Null2Space = strValue.ToString End Try End FunctionEnd ClassPublic Class DBSQLRS Public Shared Function SetDBCon( _ ByRef dbCon As SqlConnection, _ ByRef strError As String, _ Optional ByVal dbName As String = "*****", _ Optional ByVal dbUser As String = "*****" _ ) As Boolean Try If dbUser = "ELI_Web" Then dbCon = New SqlConnection("*****") ElseIf dbUser = "ELI_URP" Then dbCon = New SqlConnection("*****") End If dbCon.Open() SetDBCon = True Catch ex As Exception strError = ex.Message SetDBCon = False End Try End Function Public Shared Function SetDBCmd( _ ByRef dbCon As SqlConnection, _ ByRef dbCmd As SqlCommand, _ ByVal strCmd As String, _ ByRef strError As String _ ) As Boolean Try dbCmd = New SqlCommand(strCmd, dbCon) dbCmd.CommandType = Data.CommandType.StoredProcedure SetDBCmd = True Catch ex As Exception strError = ex.Message SetDBCmd = False End Try End Function Public Shared Function SetDBPar( _ ByRef dbCmd As SqlCommand, _ ByRef dbPar As SqlParameter, _ ByVal strParName As String, _ ByVal objParValue As Object, _ ByVal dbType As Data.SqlDbType, _ ByRef strError As String _ ) As Boolean Try dbPar = New SqlParameter(strParName, dbType) dbPar.IsNullable = True dbPar.Value = objParValue dbCmd.Parameters.Add(dbPar) SetDBPar = True Catch ex As Exception strError = ex.Message SetDBPar = False End Try End Function Public Shared Sub DisposeOfObjects( _ Optional ByRef dbCon As SqlConnection = Nothing, _ Optional ByRef dbCmd As SqlCommand = Nothing, _ Optional ByRef dbPar As SqlParameter = Nothing, _ Optional ByRef dbRdr As SqlDataReader = Nothing, _ Optional ByRef dbAda As SqlDataAdapter = Nothing, _ Optional ByRef dbDSet As DataSet = Nothing, _ Optional ByRef dTable As DataTable = Nothing, _ Optional ByRef dView As DataView = Nothing _ ) Try If Not IsNothing(dbDSet) Then dbDSet.Dispose() dbDSet = Nothing End If Catch ex As Exception dbDSet = Nothing End Try Try If Not IsNothing(dbAda) Then dbAda.Dispose() dbAda = Nothing End If Catch ex As Exception dbAda = Nothing End Try Try If Not IsNothing(dbRdr) Then If Not dbRdr.IsClosed Then dbRdr.Close() dbRdr = Nothing End If Catch ex As Exception dbRdr = Nothing End Try Try If Not IsNothing(dbPar) Then dbPar = Nothing End If Catch ex As Exception dbPar = Nothing End Try Try If Not IsNothing(dbCmd) Then dbCmd.Dispose() End If Catch ex As Exception dbCmd = Nothing End Try Try If Not IsNothing(dbCon) Then If dbCon.State = ConnectionState.Open Then dbCon.Close() End If dbCon.Dispose() End If Catch ex As Exception dbCon = Nothing End Try Try If Not IsNothing(dTable) Then dTable.Dispose() dTable = Nothing End If Catch ex As Exception dTable = Nothing End Try Try If Not IsNothing(dView) Then dView.Dispose() dView = Nothing End If Catch ex As Exception dView = Nothing End Try End Sub Public Shared Function Null2Space( _ ByVal strValue As Object, _ Optional ByVal blnDash As Boolean = False _ ) As String Try If IsNothing(strValue) Then If blnDash Then Null2Space = " - " Else Null2Space = " " End If ElseIf strValue Is System.DBNull.Value Then If blnDash Then Null2Space = " - " Else Null2Space = " " End If ElseIf strValue.ToString.Trim.Length() < 1 Then If blnDash Then Null2Space = " - " Else Null2Space = " " End If Else Null2Space = strValue.ToString.Trim() End If Catch ex As Exception Null2Space = strValue.ToString End Try End FunctionEnd Class
Query using specific results from first query to grab additional data

I have been trying the following query, the goal is to get the result from A then using the specific information from A (Customer number and Part) pull in the previous years information based off the Part.

I have been trying different variations but cannot get this to work.

Thoughts?

Select * from Table Awhere CODE = 'YYY' and Year (END_DATE)> '2015' OR Part like 'Z%' AND plan like '5%' and Year (END_DATE)> '2015')AS AWHERE (EXISTS (SELECT * from ( Select * from Table B) as BWHERE B.Part = A.PArt AND B.CUST_NUM = A.CUST_NUM AND YEAR(B.Start_date) = YEAR(A.Start_Date) - 1 )
DB2 SQL Compare on JOIN

I have the following data:

TABLE1: ADDRESS INTER1 INTER212345 E AVE STREET 44444 55555555888 OtherStreet 44444 55555555TABLE2: ADDRESS INTER1 INTER212345 E AVE STREET 44444 55555555

I can probably resolve in code this way:

if(tblOneInterOne == tblTwoInterOne ) { // compare address }

, but hoping to resolve in SQL.

SELECT A.ADDRESS , A.INTER1 , A.INTER2FROM TABLE1 AS A LEFT JOIN TABLE2 AS B ON A.INTER1 = B.INTER1 AND A.INTER2 = B.INTER2 AND A.ADDRESS <> B.ADDRESS

When comparing with the above SQL, I still get this address: 12345 E AVE STREET on my data result. I have also tried joining with a sub-query, i have also tried grouping, and ordering by asc. I am out of ideas.

Expected output: If selecting from TABLE1, match on first two integers, if match, then compare on address. If match, do not display. If no match, then display non-match address. Vice-versa, if selecting from TABLE2, match on first two integers. If match, then compare on address, if no-match then the display should be null (no data).

Any help would be appreciated.

Thank you.

Need to find latest timestamp about the customer record which spans across multiple tables

I have customer records span across multiple tables. and each tables have updated datetimestamp column.

For example :Customer table - has basic information like name,username,dob,..Address table - work address, home addressPhones table ,Features table , etc..

so when address is getting updated/added other tables records are not updated.

Here I need to find when a particular customer has got updated, It could be address alone , phones data alone.. like that..

my sql need to provide me - the latest updated datetimestamp for the given customer.

I was checking by joining all these table and used Max(update datetimestamp column of each table)..

but at the end of it , I need only the latest date..

Could you provide me the sql which would help achieving this ?

Note : This is for DB2.

Output the result of a SELECT executed on IBM DB2

It's easy to do it using T-SQL (SQL Server):

DECLARE @MyStatement AS NVARCHAR(max) = 'SELECT * FROM MYTABLE'EXEC (@MyStatement)

However, I've spend hours on IBM DB2 without being able to do the same. I was hoping this would work:

DECLARE myStatement VARCHAR(1000);SET myStatement = 'SELECT * FROM MYTABLE';PREPARE s1 FROM myStatement;EXECUTE s1;

But I get the following error message:

Elément syntaxique VARCHAR n'est pas correct. Eléments possibles : DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE.. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.18.60

which I would translate to:

Syntax element VARCHAR is incorrect. Possible elements: DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE.. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.18.60

how to update multiple column using update and case statement in db2

I want to update two column using case statement please suggest to me how to achieve this.

I have tried below piece of code but which giving syntax error:

UPDATE SASDB.TEMP_VALIDATE_FIN_ENTRIES (CASE WHEN (LENGTH(TRIM(ACCOUNT_ID)) = 16 AND NVL(LENGTH(TRIM(TRANSLATE(UPPER(ACCOUNT_ID),'0123456789',' '))),0)=0 AND LENGTH(TRIM(SOL_ID)) = 4 AND NVL(LENGTH(TRIM(TRANSLATE(UPPER(SOL_ID),'0123456789',' '))),0)=0 AND NVL(LENGTH(TRIM(TRANSLATE(UPPER(DEBIT_TRANS_AMT),'0123456789,.',' '))),0)=0 AND NVL(LENGTH(TRIM(TRANSLATE(UPPER(CREDIT_TRANS_AMT),'0123456789,.',' '))),0)=0) THEN SET VALIDATION_REMARKS='ACCOUNTS ARE VALIDATED SUCCESSFULLY' SET VALIDATION_STATUS='YES' WHEN (LENGTH(TRIM(ACCOUNT_ID)) <> 16) THEN SET VALIDATION_REMARKS='ACCOUNT_ID SHOULD BE LENGTH 16' SET VALIDATION_STATUS='NO' WHEN LENGTH(TRIM(SOL_ID)) <> 4 THEN SET VALIDATION_REMARKS='SOL_ID HAS NOT LENGTH 4' SET VALIDATION_STATUS='NO' WHEN NVL(LENGTH(TRIM(TRANSLATE(UPPER(SOL_ID),'0123456789',' '))),0)>0 THEN SET VALIDATION_REMARKS='SOL_ID HAS ALPHABET OR SPECAIL CHARACTER' SET VALIDATION_STATUS='NO' WHEN NVL(LENGTH(TRIM(TRANSLATE(UPPER(ACCOUNT_ID),'0123456789',' '))),0)>0 THEN SET VALIDATION_REMARKS='ACCOUNT_ID HAS ALPHABET OR SPECAIL CHARACTER' SET VALIDATION_STATUS='NO' WHEN NVL(LENGTH(TRIM(TRANSLATE(UPPER(CREDIT_TRANS_AMT),'0123456789,.',' '))),0)>0 THEN SET VALIDATION_REMARKS='CREDIT TRANASTION AMOUNT HAS SPECIAL CHARACTER' SET VALIDATION_STATUS='NO' WHEN NVL(LENGTH(TRIM(TRANSLATE(UPPER(DEBIT_TRANS_AMT),'0123456789,.',' '))),0)>0 THEN SET VALIDATION_REMARKS='DEBIT TRANASTION AMOUNT HAS SPECIAL CHARACTER' SET VALIDATION_STATUS='NO' WHEN ACCOUNT_EXIT_FLAG IS NULL THEN SET VALIDATION_REMARKS='ACCOUNT DOESNOT EXIST IN DATABASE' SET ACCOUNT_EXIT_FLAG=NVL(ACCOUNT_EXIT_FLAG,'NO') END);

The above code is failing with syntax error please let me know how to resolve those error.

SQL help needed in removing a sub-query

I have a poorly performing SQL that contains a sub-query that is not co-related. I replaced it with a co-related sub-query but performance got worse. Is there a way to rewrite this SQL by removing the sub-query entirely and replacing it with a join?

Below is a simplified version of the query:

 select distinct tab1.app_id, tab1.name, tab1.stat_cd, tab1.qr from apps tab1 , issues tab2 where tab1.app_id = tab2.app_id and tab1.qr = 'm' and tab2.iqr = 'm' and tab1.app_id not in ( select distinct tab3.app_id from issues tab3 where tab3.iqr = 'm' and ( tab3.i_cd = 'f' or tab3.i_cd = 'r' or tab3.i_cd = 'c' ) ) 

Any tip or assistance is appreciated. Thank you.

SQL join two tables using the minimum value from second table

I have T1

ID STATUS1 NEW2 NEW

I have T2

T1ID OWNER OWNDATE1 A 01.01.2017 10:00:001 B 02.02.2017 10:00:001 C 03.03.2017 10:00:002 B 02.02.2017 10:00:002 C 03.03.2017 10:00:00

Since A for ID 1 has the minimum OWNDATE and since B for ID 2 has the minimum OWNDATE

I want to get as result

1 NEW A2 NEW B

But after the join I am not sure where to put MIN so I could find the minimum from two tables

SELECT T1.ID, T1.STATUS, T2.OWNERFROM T1 JOIN T2 ON T1.ID = T2.T1ID

Thank you in advance