Ошибка при запуске запросов SELECT для извлечения данных из DB2 и записи на SQL Server. ⇐ Python

[Anonymous]

У меня есть скрипт Python, который принимает несколько запросов SELECT и помещает их в отдельные таймеры с потоками, и каждый из них выполняет запросы периодически через заданные интервалы времени. У меня есть база данных SQL Express для хранения извлекаемых таблиц, и я извлекаю их все из базы данных IBM DB2. При запуске сценария я получаю следующую ошибку:

Ошибка запроса в TABLE1: ('22001', '[22001] [IBM][System i Access ODBC Driver]Столбец 1: COLUMN1 — входные данные слишком велики, чтобы поместиться в поле (30207) (SQLExecDirectW); [22001] [IBM][System i Access] Драйвер ODBC]Столбец 1: усечение справа от символьных данных (30126)')

Она показывает одну и ту же ошибку почти для каждого столбца и таблицы.
Вот мой код:
import threading
import time
import pyodbc

class DB2Worker(threading.Thread):
def __init__(self, interval, query, params, target_table, db2_conn_str, local_conn_str):
super().__init__()
self.interval = interval
self.query = query
self.params = params
self.target_table = target_table
self.db2_conn_str = db2_conn_str
self.local_conn_str = local_conn_str
self.daemon = True
self.db2_conn = None

def _get_db2_connection(self):
"""Maintains the connection to the remote DB2 source."""
while self.db2_conn is None:
try:
self.db2_conn = pyodbc.connect(self.db2_conn_str)
self.db2_conn.autocommit = True
except Exception as e:
print(f"DB2 Connection Error: {e}. Retrying in 5s...")
time.sleep(5)

def _save_to_local_sql(self, data, db2_description):
"""Dynamic mapping to Local SQL Express."""
if not data:
return

try:
column_names = [col[0] for col in db2_description]
cols_str = ", ".join(column_names)
placeholders = ",".join(["?" for _ in column_names])

insert_sql = f"INSERT INTO {self.target_table} ({cols_str}) VALUES ({placeholders})"

with pyodbc.connect(self.local_conn_str) as local_conn:
with local_conn.cursor() as cursor:
cursor.fast_executemany = True

cursor.executemany(insert_sql, data)
local_conn.commit()
print(f"[{self.target_table}] Successfully synced {len(data)} rows.")
except Exception as e:
print(f"Local SQL Save Error for {self.target_table}: {e}")

def run(self):
self._get_db2_connection()

while True:
try:
cursor = self.db2_conn.cursor()

if self.params:
cursor.execute(self.query, self.params)
else:
cursor.execute(self.query)

if cursor.description:
results = cursor.fetchall()
if results:
self._save_to_local_sql(results, cursor.description)

cursor.close()
except (pyodbc.Error, Exception) as e:
print(f"Query Error on {self.target_table}: {e}")
self.db2_conn = None
self._get_db2_connection()

time.sleep(self.interval)

DB2_CONNECTION_STRING = (
"DRIVER={iSeries Access ODBC Driver};"
"DATABASE=DB2;"
"SYSTEM=192.168.10.1;"
"PORT=446;"
"PROTOCOL=TCPIP;"
"UID=USERNAME;"
"PWD=PASSWORD;"
"ConnectTimeout=10;"
"LongDataCompat=1;"
"TrimChar=1;"
)

LOCAL_SQL_CONNECTION_STRING = (
"Driver={ODBC Driver 18 for SQL Server};"
"Server=SERVER\\SERVER;"
"Database=DB;"
"Trusted_Connection=yes;"
"Encrypt=yes;"
"TrustServerCertificate=yes;"
)

query_tasks = [
# 1. Queries WITH tags (using '?' and raw strings)
(1, 'SELECT "G1ST" FROM "DB2"."TABLE1" WHERE "G1R" = ?', (r'192.168.64.1\Tag1',), "TABLE1"),
(0.5, '''SELECT "FWF","FWCF","FWI","FWWSF3","FWWSI3" FROM "DB2"."TABLE2" WHERE "FWM" = 'LOC1' AND "FWR" = ? ORDER BY "FWF" ASC''', (r'192.168.64.1\Tag2',), "TABLE2"),
(1, 'SELECT "G1SB" FROM "DB2"."TABLE1" WHERE "G1R" = ?', (r'192.168.64.1\Tag3',), "TABLE1"),

# 2. Queries WITHOUT tags (Standard SQL)
(1.0, '''SELECT "B1R","B1B" FROM "DB2"."TABLE3" WHERE "B1W" = 'LOC3' ORDER BY "B1X" DESC , "B1T" DESC''', None, "TABLE3"),
(1.0, '''SELECT "GSR","GSS" FROM "DB2"."TABLE4" WHERE "GSM" = 'LOC1' AND "GSA" < 7 ORDER BY "GSS" ASC''', None, "TABLE4"),
(1.0, '''SELECT "GSR" FROM "DB2"."TABLE4" WHERE "GSM" = 'LOC1' AND "GSA" = 5 ORDER BY "GSS" DESC''', None, "TABLE4"),
(0.5, '''SELECT "G7G","G7F" FROM "DB2"."TABLE5" WHERE "G7CM" = 'LOC1' ORDER BY "G7CJ" DESC , "G7T" DESC''', None, "TABLE5"),
(1.5, '''SELECT "G7W","G7R","G7MAP" FROM "DB2"."TABLE6" WHERE "G7MAC" = 'LOC1' ''', None, "TABLE6"),
(1.5, '''SELECT "G7W","G7R","G7MAP" FROM "DB2"."TABLE6" WHERE "G7MAC" = 'LOC1' ''', None, "TABLE6")
]

if __name__ == "__main__":
for interval, sql, params, target_table in query_tasks:
worker = DB2Worker(interval, sql, params, target_table,
DB2_CONNECTION_STRING, LOCAL_SQL_CONNECTION_STRING)
worker.start()
time.sleep(0.5)

while True:
time.sleep(10)

Я пробовал изменить типы данных в БД с NVARCHAR(MAX) на NVARCHAR(255) или NVARCHAR(100). Я проверил, и две рабочие таблицы имеют все типы данных, которые есть в других таблицах. Я пробовал использовать обрезку для удаления пробелов, но ничего не помогло.
Это файл SQL, который я использую для создания базы данных SQL Server Express:
USE DB;
GO

IF OBJECT_ID('TABLE3', 'U') IS NOT NULL
DROP TABLE TABLE3;

CREATE TABLE TABLE3
(
B1R NVARCHAR(100),
B1B NVARCHAR(100),
B1W NVARCHAR(100),
B1X INT,
B1T INT
);

IF OBJECT_ID('TABLE1', 'U') IS NOT NULL
DROP TABLE TABLE1;

CREATE TABLE TABLE1
(
G1R NVARCHAR(100),
G1ST NVARCHAR(100),
G1SB NVARCHAR(100),
G1AF FLOAT, G1AI FLOAT, G1AT FLOAT, G1AD FLOAT,
G1CL FLOAT, G1DTYP NVARCHAR(100), G1TDTE INT, G1TMCH NVARCHAR(100),
G1DDTE INT, G1DMCH NVARCHAR(100), G1DLOT NVARCHAR(100),
G1TWGT FLOAT, G1PRDG NVARCHAR(100), G1SIZE NVARCHAR(100),
G1CM NVARCHAR(255)
);

IF OBJECT_ID('TABLE7', 'U') IS NOT NULL
DROP TABLE TABLE7;

CREATE TABLE TABLE7
(
FYS NVARCHAR(100),
FYC NVARCHAR(100)
);

IF OBJECT_ID('TABLE8', 'U') IS NOT NULL
DROP TABLE TABLE8;

CREATE TABLE TABLE8
(
FZST NVARCHAR(100),
FZSI NVARCHAR(100),
FZB NVARCHAR(100),
FZR INT,
FZG FLOAT, FZTW FLOAT, FZTS FLOAT, FZTH FLOAT,
FZT FLOAT, FZTL FLOAT, FZSTE INT, FZPAT NVARCHAR(100),
FZL FLOAT, FZQTY1 FLOAT, FZL2 FLOAT, FZY FLOAT,
FZI INT
);

IF OBJECT_ID('TABLE9', 'U') IS NOT NULL
DROP TABLE TABLE9;

CREATE TABLE TABLE9
(
FZS NVARCHAR(100),
FZD NVARCHAR(100),
FZF NVARCHAR(100)
);

IF OBJECT_ID('TABLE10', 'U') IS NOT NULL
DROP TABLE TABLE10;

CREATE TABLE TABLE10
(
FYM NVARCHAR(100),
FYS NVARCHAR(100),
FYO FLOAT
);

IF OBJECT_ID('TABLE11', 'U') IS NOT NULL
DROP TABLE TABLE11;

CREATE TABLE TABLE11
(
G51S NVARCHAR(100),
G51I NVARCHAR(100)
);

IF OBJECT_ID('TABLE4', 'U') IS NOT NULL
DROP TABLE TABLE4;

CREATE TABLE TABLE4
(
GSM NVARCHAR(100),
GSR NVARCHAR(100),
GSS INT,
GSA INT
);

IF OBJECT_ID('TABLE5', 'U') IS NOT NULL
DROP TABLE TABLE5;

CREATE TABLE TABLE5
(
G7G NVARCHAR(100),
G7F NVARCHAR(100),
G7CM NVARCHAR(100),
G7CJ INT,
G7T INT
);

IF OBJECT_ID('TABLE2', 'U') IS NOT NULL
DROP TABLE TABLE2;

CREATE TABLE TABLE2
(
FWM NVARCHAR(100),
FWR NVARCHAR(100),
FWF INT,
FWCF FLOAT, FWI FLOAT, FWWSF3 FLOAT, FWWSI3 FLOAT
);

IF OBJECT_ID('TABLE6', 'U') IS NOT NULL
DROP TABLE TABLE6;

CREATE TABLE TABLE6
(
G7MAC NVARCHAR(100),
G7W NVARCHAR(100),
G7R FLOAT,
G7MAP FLOAT
);