Topic: Databases
MS SQL Server
- Client errors:
[Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [5].
[Microsoft][SQL Server Native Client 11.0]Login timeout expired
Solution: Use SQL Server Configuration Manager (an mmc dialog). Expand the network connectivity section on the left. Enable TCP/IP (or named pipes, if that’s what you want).
- MS SQL Server “mixed mode” authenticaton
- Switch SQL Server authentication mode
- Check basic connectivity to instance name
- Determine instance name and version
- List databases:
sqlcmd -S localhost -U sa -P Passport1 -Q "select name from sys.databases" - Table: Describe table:
exec sp_help '$DISTRICT_@4_RecordLocks$' - “who” has SQL Server sessions running?
(Be careful using the query feature in the SQL Server Management UI. This is a session, and also prevents you from deleting the database, until you kill that query session). - Indexes:
- View indexes:
EXEC sp_helpindex 'Customer'
GO
Note: It looks like a restore of a db backup with indexes with restore/create the indexes again. - Dropping indexes:
This doesn’t look very easy to automate/script, so I will use the SQL Server UI for now (expand each table to see the indexes, but don’t delete the primary key indexes).
- View indexes:
- Example Queries:
- To run an adhoc query, right-click on the database name and select “New Query”.
- How to specify the table:
select finalresult from [tpccresults].[dbo].[tpcc_data] Where finalresult IS NOT NULL group by finalresult order by Max(RIGHT(REPLICATE(N' ', 500) + package, 500)) DESC - Show table schema:
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='tpcc_data' - SQL Server has no “LIMIT” query clause! You need to use “TOP”, e.g.:
select top 5 test_id,test_params,test_luid32,test_luid64 from [tpccresults].[dbo].[tpcc_data] order by test_id desc - SQL Server update example:
update [tpccresults].[dbo].[tpcc_data] set test_type = 'SILK_PAC' where test_id = 3597 and test_type = 'SILK_ZIP'
- odbcconf.exe (soon to be deprecated by PowerShell)
- ODBC Driver “SQL Server” vs “SQL Server Native Client”
- SQL Server Log
- Forcibly close all connections to database
- SQL Server Single User mode
PostgreSQL
- General PG admin (including creating new users, aka “roles”)
- Adding specific access (least privilege model)
- Optimization and Tuning
- Downloads
- PG transactions (xact_commit and xact_rollback)
- See config values:
psql -U postgres postgres -c "show all;" | grep cost - Version:
C:\>psql --version
psql (PostgreSQL) 10.11 - Locate main config file:
PGPASSWORD=fred psql -U escc postgres -c "show config_file;"
config_file
----------------------------------------
/var/lib/pgsql/10/data/postgresql.conf - Locate hba (host-based-authentication) file:
psql -U postgres
postgres=# show hba_file - List connections to a database:
Colin: “To see what the connections to the database are, can you run the following query against the cross-region database”:
psql -U postgres "MicroFocus\$CAS\$CrossRegion" -c "SELECT * FROM pg_stat_activity" - List databases - one-liner:
` psql -U postgres -c “select datname from pg_database”` - List databases, then quit:
psql -U postgres
\list
\q - List databaases from pg_database:
postgres=# select datname from pg_database; datname ------------------------------ postgres template1 template0 MicroFocus$CAS$CrossRegion MicroFocus$CAS$Region$MYPAC MicroFocus$SEE$Files$VSAM MicroFocus$CAS$Region$MYPAC2 (7 rows) - pgpass.conf:
Note: pgpass.conf simply provides the password for you.
C:\>type %APPDATA%\postgresql\pgpass.conf
localhost:5432:*:postgres:Passport1! - dump single database:
/usr/pgsql-10/bin/pg_dump -U escc -W -F t 'MicroFocus$SEE$Files$MLVSAM' > MLVSAM.tar - Restore single database dump:
NOTE: The pg_restore command is very confusing!!! If you are importing the backup archive into the same database, then you need to use “–dbname=postgres”. This is because the name of the database being restored is actually in the archive file. But, if you want to import the backup archive into a differently-named database, then you use “–dbname==", but you will have to create the database first yourself! See my script `pg_backup.sh` - Add a user
- Create a user with postgres privileges:
psql -U postgres postgres # \du # create user escc with encrypted password 'CC1adm1n'; # alter user escc with SUPERUSER CREATEDB REPLICATION;
- Create a user with postgres privileges:
- PG Replication status
- Vacuum and analyze (includes query to show last of both)
IBM DB2
- (IBM) DB2:
- clpplus (Note: Oracle uses “sqlplus”):
- List databases:
<I haven't found a way to do this yet!> - Connect to a database (as per DB2 on the tpcc 6-pair):
clpplus testing/password@localhost/tpcc - db2dsdriver.cfg:
“db2dsdriver.cfg is an XML file that contains a list of DSN aliases and their properties. It is used to store connection details in one place.”
- List databases:
- clpplus (Note: Oracle uses “sqlplus”):