Conectividad Externa

# En Linux/Mac: verificar que el driver esta registrado
odbcinst -q -d
# Debe mostrar: [IBM i Access ODBC Driver]

# En Windows: Panel de Control > ODBC Data Sources
# O ejecutar: odbcad32.exe

# Ubicacion tipica del driver en Windows:
# C:Program FilesIBMIBM i Access Client SolutionsODBCin
# Ubicacion del JAR JDBC:
# C:Program FilesIBMIBM i Access Client SolutionsJavajt400.jar

// build.gradle o pom.xml
// implementation 'com.zaxxer:HikariCP:5.0.1'
// implementation 'net.sf.jt400:jt400:20.0.7'

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:as400://IBMI_HOST/MILIB");
config.setUsername("MIUSUARIO");
config.setPassword("mipassword");
config.setDriverClassName("com.ibm.as400.access.AS400JDBCDriver");

// Pool settings
config.setMinimumIdle(5);
config.setMaximumPoolSize(20);
config.setConnectionTimeout(30000);    // 30 seg
config.setIdleTimeout(300000);          // 5 min
config.setMaxLifetime(1800000);         // 30 min
config.setConnectionTestQuery("VALUES 1");

// Propiedades especificas de IBM i
config.addDataSourceProperty("naming", "sql");
config.addDataSourceProperty("date format", "iso");
config.addDataSourceProperty("libraries", "MILIB OTRALIB");
config.addDataSourceProperty("transaction isolation", "read committed");

HikariDataSource ds = new HikariDataSource(config);

# Connection string completa para IBM i Access ODBC
Driver={IBM i Access ODBC Driver};
System=IBMI_HOST;
UID=MIUSUARIO;
PWD=mipassword;
DefaultLibraries=MILIB OTRALIB;
Naming=1;
DateFormat=5;
DateSeparator=/;
TimeFormat=1;
CommitMode=2;
ConnectionType=0;
AllowUnsupportedChar=1;
BlockFetch=1;
BlockSizeKB=512;
TrueAutoCommit=1;
ForceTranslation=0;
CCSID=1208;

import pyodbc

# Connection string
conn_str = (
    "Driver={IBM i Access ODBC Driver};"
    "System=IBMI_HOST;"
    "UID=MIUSUARIO;"
    "PWD=mipassword;"
    "DefaultLibraries=MILIB;"
    "Naming=0;"           # SQL naming (schema.tabla)
    "DateFormat=5;"       # ISO date format
    "CommitMode=2;"       # Read committed
    "CCSID=1208;"         # UTF-8
)

conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# Ejecutar query
cursor.execute("""
    SELECT ID, NOMBRE, SALDO
    FROM MILIB.CLIENTES
    WHERE ESTADO = ?
    ORDER BY NOMBRE
""", ('A',))

for row in cursor.fetchall():
    print(f"{row.ID}: {row.NOMBRE} - ${row.SALDO}")

cursor.close()
conn.close()

# URL basica
jdbc:as400://IBMI_HOST/MILIB

# URL con propiedades inline
jdbc:as400://IBMI_HOST/MILIB;
  naming=sql;
  date format=iso;
  time format=iso;
  libraries=MILIB OTRALIB;
  transaction isolation=read committed;
  translate binary=true;
  prompt=false;
  block size=512;
  lazy close=true

# Driver class
com.ibm.as400.access.AS400JDBCDriver

# Maven dependency
# <dependency>
#   <groupId>net.sf.jt400</groupId>
#   <artifactId>jt400</artifactId>
#   <version>20.0.7</version>
# </dependency>

# application.properties para Spring Boot
spring.datasource.url=jdbc:as400://IBMI_HOST/MILIB;\
  naming=sql;date format=iso;libraries=MILIB;\
  transaction isolation=read committed
spring.datasource.username=MIUSUARIO
spring.datasource.password=mipassword
spring.datasource.driver-class-name=\
  com.ibm.as400.access.AS400JDBCDriver

# HikariCP pool settings
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-test-query=VALUES 1

// NuGet: Install-Package IBM.Data.DB2.iSeries

using IBM.Data.DB2.iSeries;

var connStr = "DataSource=IBMI_HOST;" +
              "UserID=MIUSUARIO;" +
              "Password=mipassword;" +
              "DefaultCollection=MILIB;" +
              "Naming=sql;" +
              "DateFormat=ISO;" +
              "TrueAutoCommit=true;";

using var conn = new iDB2Connection(connStr);
conn.Open();

using var cmd = conn.CreateCommand();
cmd.CommandText = @"
    SELECT ID, NOMBRE, SALDO
    FROM MILIB.CLIENTES
    WHERE ESTADO = @estado
    ORDER BY NOMBRE";
cmd.Parameters.AddWithValue("@estado", "A");

using var reader = cmd.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(
        $"{reader.GetInt32(0)}: " +
        $"{reader.GetString(1)} - " +
        $"${reader.GetDecimal(2)}"
    );
}

/* Exportar tabla a CSV delimitado */
CPYTOIMPF FROMFILE(MILIB/CLIENTES) +
          TOSTMF('/home/exports/clientes.csv') +
          MBROPT(*REPLACE) +
          STMFCCSID(1208) +
          RCDDLM(*CRLF) +
          DTAFMT(*DLM) +
          STRDLM('"') +
          FLDDLM(',') +
          ADDCOLNAM(*SQL)

/* Importar CSV a tabla */
CPYFRMIMPF FROMSTMF('/home/imports/nuevos_clientes.csv') +
           TOFILE(MILIB/CLIENTES) +
           MBROPT(*ADD) +
           RCDDLM(*CRLF) +
           DTAFMT(*DLM) +
           STRDLM('"') +
           FLDDLM(',')

-- Insertar datos desde una consulta SQL
INSERT INTO MILIB.CLIENTES_BACKUP
  SELECT * FROM MILIB.CLIENTES
  WHERE FECHA_CREACION < CURRENT_DATE - 365 DAYS;

-- Crear tabla a partir de query (CTAS)
CREATE TABLE MILIB.RESUMEN_ANUAL AS (
  SELECT YEAR(FECHA) AS ANIO,
         REGION, COUNT(*) AS TOTAL,
         SUM(MONTO) AS MONTO_TOTAL
  FROM MILIB.VENTAS
  GROUP BY YEAR(FECHA), REGION
) WITH DATA;

-- Usando IFS para leer archivos
-- (requiere configuracion previa)
CREATE TABLE QTEMP.DATOS_IMPORT AS (
  SELECT * FROM
  TABLE(QSYS2.IFS_READ_UTF8(
    PATH_NAME => '/home/datos/input.csv'
  ))
) WITH DATA;

-- Conectar a otro sistema IBM i via DRDA
CONNECT TO SYSDR;

-- Consultar tabla remota
SELECT * FROM REMOTELIB.CLIENTES
WHERE REGION = 'NORTE';

-- Desconectar
DISCONNECT SYSDR;

-- Three-part naming (sin CONNECT explicito)
-- Requiere configuracion previa en WRKRDBDIRE
SELECT *
FROM SYSDR.REMOTELIB.CLIENTES
WHERE ESTADO = 'A';

-- Copiar datos entre sistemas
INSERT INTO MILIB.CLIENTES_COPIA
  SELECT * FROM SYSDR.REMOTELIB.CLIENTES
  WHERE FECHA_ALTA > '2025-01-01';

/* Agregar entrada de remote database */
ADDRDBDIRE RDB(SYSDR) +
           RMTLOCNAME('10.1.1.50' *IP) +
           PORT(446) +
           TEXT('Sistema DR en datacenter B')

/* Verificar conexion */
WRKRDBDIRE

/* Probar conectividad */
STRSQL
CONNECT TO SYSDR USER MIUSUARIO USING 'mipassword'

# JDBC con SSL
jdbc:as400://IBMI_HOST/MILIB;
  secure=true;
  naming=sql;
  date format=iso

# ODBC con SSL
Driver={IBM i Access ODBC Driver};
System=IBMI_HOST;
SSL=1;
DefaultLibraries=MILIB;
Naming=0;
CCSID=1208;

# .NET con SSL
DataSource=IBMI_HOST;
DefaultCollection=MILIB;
SSL=true;
Naming=sql;

-- Ver conexiones activas al IBM i
SELECT
  JOB_NAME,
  AUTHORIZATION_NAME AS USUARIO,
  CLIENT_IP_ADDRESS AS IP_CLIENTE,
  FUNCTION_TYPE,
  JOB_STATUS,
  ELAPSED_CPU_PERCENTAGE AS CPU_PCT
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(
  SUBSYSTEM_LIST_FILTER => 'QUSRWRK',
  DETAILED_INFO => 'ALL'
)) AS j
WHERE FUNCTION_TYPE = 'DATABASE'
ORDER BY ELAPSED_CPU_PERCENTAGE DESC;

-- Conexiones DRDA activas
SELECT
  JOB_NAME, AUTHORIZATION_NAME,
  CLIENT_IP_ADDRESS,
  FUNCTION AS PROGRAMA,
  SQL_STATEMENT_STATUS
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(
  SUBSYSTEM_LIST_FILTER => 'QSERVER',
  DETAILED_INFO => 'ALL'
)) AS j
WHERE JOB_TYPE = 'SBS'
ORDER BY JOB_NAME;