Metadata-Version: 2.4
Name: db2ssh
Version: 1.0.0
Summary: Query IBM i Db2 databases over SSH — no ibm_db driver required
Author: dtg01100
License-Expression: MIT
Project-URL: Homepage, https://github.com/dtg01100/db2ssh
Project-URL: Repository, https://github.com/dtg01100/db2ssh
Project-URL: Issues, https://github.com/dtg01100/db2ssh/issues
Keywords: db2,ibm,ibm-i,ssh,database,sql,as400
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.7
Classifier: Programming Language :: Python :: 3.8
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Requires-Python: >=3.7
Description-Content-Type: text/markdown
Requires-Dist: paramiko>=2.7
Provides-Extra: dev
Requires-Dist: pytest>=7.0; extra == "dev"

# db2ssh

Query IBM i Db2 databases over SSH — no `ibm_db` driver required.

## Why

The `ibm_db` Python driver bundles IBM's proprietary ODBC/CLI libraries, which creates licensing friction, installation headaches, and platform compatibility issues. This project takes a different approach: connect to the IBM i via SSH and use its built-in `db2` command-line tool.

## Requirements

- Python 3.7+
- SSH access to the target IBM i system

## Installation

```
pip install db2ssh
```

## Usage

### DB-API 2.0 Driver

Drop-in replacement pattern for code that expects a PEP 249 database interface:

```python
from db2ssh import connect

conn = connect(host="your-ibm-i.example.com", user="myuser", password="mypass")

cur = conn.cursor()
cur.execute("SELECT TABLE_NAME, TABLE_TYPE FROM QSYS2.SYSTABLES WHERE TABLE_TYPE = ? FETCH FIRST 10 ROWS ONLY", ['L'])

for row in cur.fetchall():
    print(row)

conn.close()
```

With context manager:

```python
with connect(host="your-ibm-i.example.com", user="myuser", password="mypass") as conn:
    cur = conn.cursor()
    cur.execute("SELECT COUNT(*) FROM QSYS2.SYSTABLES")
    print(cur.fetchone())
```

With SSH key:

```python
conn = connect(host="your-ibm-i.example.com", user="myuser", key_filename="~/.ssh/id_rsa")
```

Without password or key, paramiko will try SSH agent and default keys (`~/.ssh/id_rsa`, etc.):

```python
conn = connect(host="your-ibm-i.example.com", user="myuser")
```

### CLI Tool

After `pip install db2ssh`, the `db2ssh` command is available:

```bash
# Password auth
db2ssh --host your-ibm-i.example.com --user myuser --password mypass \
  --query "SELECT * FROM QSYS2.SYSTABLES FETCH FIRST 5 ROWS ONLY"

# Key-based auth
db2ssh --host your-ibm-i.example.com --user myuser \
  --key-file ~/.ssh/id_rsa \
  --query "SELECT * FROM QSYS2.SYSTABLES FETCH FIRST 5 ROWS ONLY"

# From a SQL file
db2ssh --host your-ibm-i.example.com --user myuser --password mypass \
  --file queries.sql

# Save output to file
db2ssh --host your-ibm-i.example.com --user myuser --password mypass \
  --query "SELECT * FROM QSYS2.SYSTABLES" --output results.txt

# Password via environment variable
export DB2_PASSWORD=mypass
db2ssh --host your-ibm-i.example.com --user myuser \
  --query "SELECT CURRENT_DATE FROM SYSIBM.SYSDUMMY1"
```

## How It Works

1. Opens an SSH connection to the IBM i using paramiko
2. Writes the SQL statement to a temporary file on the remote system
3. Executes the query via `qsh -c "db2 -f <file>"`
4. Parses the fixed-width columnar output into structured results
5. Cleans up the remote temp file

No software needs to be installed on the IBM i beyond its default SSH server and the built-in `db2` command.

## DB-API 2.0 Compliance

| Feature | Status |
|---------|--------|
| `connect()` | Implemented |
| `Connection.cursor()` | Implemented |
| `Cursor.execute()` | Implemented |
| `Cursor.executemany()` | Implemented |
| `Cursor.fetchone()` | Implemented |
| `Cursor.fetchmany()` | Implemented |
| `Cursor.fetchall()` | Implemented |
| Iterator protocol | Implemented |
| Context manager | Implemented |
| `?` (qmark) parameter style | Implemented |
| Error hierarchy | Implemented |
| `commit()` | No-op (IBM i autocommit) |
| `rollback()` | Raises `NotSupportedError` |

## Error Handling

All errors from the IBM i are raised as DB-API 2.0 exceptions:

```python
from db2ssh import connect, ProgrammingError, OperationalError

try:
    conn = connect(host="bad-host", user="user", password="pass")
except OperationalError as e:
    print(f"Connection failed: {e}")

try:
    cur.execute("SELECT * FROM NONEXISTENT.TABLE")
except ProgrammingError as e:
    print(f"Query failed: {e}")
```

## Security Notes

- **Authentication**: Supports password auth and SSH key-based auth. Key auth is recommended for production — use `--key-file` or pass `key_filename` to `connect()`. If no password or key is specified, paramiko will try your SSH agent and default keys (`~/.ssh/id_rsa`, etc.).
- **Passwords**: Use the `DB2_PASSWORD` environment variable or interactive prompt rather than passing passwords on the command line.
- **Host key verification**: The default configuration auto-accepts unknown SSH host keys (`AutoAddPolicy`). For production use, consider implementing strict host key checking.
- **Temp files**: A uniquely-named SQL file (`/tmp/.db2ssh_<uuid>.sql`) is written to the IBM i during each query execution and deleted immediately after. The UUID-based name prevents collision between concurrent executions and eliminates symlink-based TOCTOU attacks.

## Limitations

- Result sets only: DDL/DML statements that don't return rows will execute but won't report affected row counts accurately.
- `NULL` literal: Bare `SELECT NULL` may not work on some IBM i versions. Use `CAST(NULL AS <type>)` instead.
- No transactions: `commit()` is a no-op, `rollback()` raises `NotSupportedError`.
- Single-threaded: Each `Connection` object holds one SSH session. Share across threads at the module level only (`threadsafety=1`).
- No SSL/TLS: SSH transport is encrypted, but the db2 connection from the IBM i to its own database is local.

## License

MIT
