Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
Azure DB2 Linked service setup assistance (AS400)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(227.2, 24%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EJR%3C/text%3E%3C/svg%3E)
Jose Rivera
0
Reputation points
DB2 Linked service in Azure Synapse Analytics. Goal is to copy data from AS400 system to an Azure data lake storage.
Unfortunately, I haven't found an example of DB2 linked service and connection properties to ensure settings is correct. I'm having a connection error.
Configuration and Current Error images included.
Azure Synapse Analytics
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(54.400000000000006, 12%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESV%3C/text%3E%3C/svg%3E)
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator2024-02-14T04:34:04.2266667+00:00 Are you not giving any password in the password field? This error code is mostly w.r.to authorization/authentication. So I wonder if there's any issue with the password.
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(12.8, 48%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EH%3C/text%3E%3C/svg%3E)
Harishga 6,005 Reputation points Microsoft External Staff2024-02-14T09:25:38.2466667+00:00 Hi @Jose Rivera
Welcome to Microsoft Q&A platform and thanks for posting your question here.
I understand that you are trying to copy data from an AS400 system to an Azure data lake storage, but encountering an error related to the DB2 linked service in Azure Synapse Analytics.
To troubleshoot this issue, you need to ensure that the DB2 linked service and connection properties are correctly configured.
- Verify the accuracy of the connection string in the linked service configuration. This involves ensuring that the connection string is in the correct format and has all the necessary parameters. Any errors or omissions in the connection string can prevent the Integration Runtime from being set up correctly.
- Check the firewall rules on the AS400 system It is important to confirm that the firewall rules on the AS400 system allow traffic from the Azure Synapse Analytics workspace. This can be done by checking the IP address of the workspace in the networking section of the workspace.
- Ensure that the credentials used in the linked service configuration are correct It is crucial to ensure that the credentials used in the linked service configuration are correct and have the required permissions to access the DB2 database on the AS400 system. Any errors or omissions in the credentials can prevent the Integration Runtime from being set up correctly.
I hope this information helps you. Let me know if you have any further questions or concerns.
-
Harishga 6,005 Reputation points Microsoft External Staff
2024-02-15T06:02:05.79+00:00 Hi @Jose Rivera
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help. -
Jose Rivera 0 Reputation points
2024-02-15T13:03:56.79+00:00 Reviewing your recommendations.
-
Jose Rivera 0 Reputation points
2024-02-15T13:51:38.4766667+00:00 I was able to configure the Microsoft Integration. During connection testing this error.
-
Jose Rivera 0 Reputation points
2024-02-15T13:51:50.46+00:00 -
Jose Rivera 0 Reputation points
2024-02-15T20:26:01.46+00:00 Hi, I was able to create the Self-Host Integration Runtime (DB2 On-Prem) and DB2 Linked Services with (Successfully Connections). Now During the DataSet configuration I'm getting this error when Listing Tables List or Showing a table Preview. Do we need to install a Cumulative Package ?
-
Jose Rivera 0 Reputation points
2024-02-15T21:17:41.79+00:00 I was able to create the DB2 Self-Host environment and the DB2 link-services with Successfully Connections. Now during the Set-properties configuration i'm getting error -379 listing the TABLES NAME or Schemas.
-
Harishga 6,005 Reputation points Microsoft External Staff
2024-02-16T07:42:05.8+00:00 @Jose Rivera
The error code -379 in DB2 is caused by a network level syntax error. This error is associated with the SQLSTATE=HY000 and SQLCODE=-379. When you encounter this error during the set-properties configuration, it usually means there is an issue with the table name or schema syntax.In your case, it seems that the error is related to the syntax of the SQL statement used to list the table names or schemas. It is possible that there is a syntax error in the SQL statement or that the statement is not properly formed. To resolve this issue, you can try the following steps:
- Check if the SQL statement used to list the table names or schemas is correct and has no errors.
- Ensure that there are no network issues between the DB2 Self-Host environment and the DB2 link-services.
- Check if the user account used to access the DB2 Self-Host environment has the necessary permissions to list the table names or schemas.
- Verify that the version of DB2 being used is compatible with the version of the DB2 link-services being used.
- Check the DB2 error logs for more detailed information on the internal network library error, which can provide further insights into the root cause of the issue.
I hope this information helps you. Let me know if you have any further questions or concerns.
-
Jose Rivera 0 Reputation points
2024-02-16T19:27:51.6166667+00:00 I checked all user permissions, ports and user global setup and everything looks ok on the iSeries. I also have changed the connection mode from DB2 to ODBC and noticed that the connection is fine and The tables are being listed during the dataset creation. But the data preview is showing a different error. Made an online search and the suggestion is to fix any formula but In this case I'm just reading a table. Data-Set creation: Screen
Error Screen:
-
Harishga 6,005 Reputation points Microsoft External Staff
2024-02-19T10:38:44.2933333+00:00 @Jose Rivera
When previewing data in Azure, an arithmetic overflow error can occur if the data contains numeric values that exceed the maximum or minimum value that can be stored in the data type being used. This can happen if the data type is not suitable to hold the values in the column. To resolve this issue, you can try the following steps:- Check the data type of the column that is causing the error. Ensure that it is large enough to accommodate the values in the column.
- If the data type is correct, check the values in the column to ensure that they are within the range of the data type.
- If the values are correct, you can try casting the column to a larger data type to accommodate the values.
I hope this helps!
Sign in to comment
Sign in to answer