Problems creating DB2 UDB LUW db from GUI tools

I've had tons of problems creating db's (or doing some other operations) from the GUID tools, including Data Studio, and the control center (under db2 9.7).

It's always complained about the DAS, but the DAS (the DB2 Administration Server) is always happily running.

Today I stumbled across something that told me to issue the following command:

db2admin create

I then ran a

db2admin start

!!!! WORKED LIKE A CHARM !!!!

After that, I was able to successfully verify the instance, connect, and create the db I was trying to create.

Sorry - wish I had captured some screenshots along the way, but now that I've fixed it, I can't reproduce ;-)

At any rate, hope this helps someone.

Getting read-only access to DMV's for querying system and performance stats

Most of what we're going to want to look at when you're having production issues are available through DMV's.

  

If granting select to sys.dm* is a bit too much, these are the tables I'm generally most interested in:

Required Permissions:

To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.

For Query tuning:

sys.dm_exec_query_stats

sys.dm_exec_sql_text

sys.dm_exec_text_query_plan

For general Waits and locks:

sys.dm_tran_locks

sys.dm_os_wait_stats

For Missing Indexes:

sys.dm_db_missing_index_group_stats

sys.dm_db_missing_index_groups

sys.dm_db_missing_index_details

sys.databases

For IO_STALL:

sys.dm_io_virtual_file_stats

sys.master_files

/* NOTE: The following should not be needed as users are already set up

USE [master]

GO

CREATE USER [theUser] FOR LOGIN [theUser] WITH DEFAULT_SCHEMA=[dbo]

GO

USE [master]

GO

GRANT CONNECT TO [theUser] 

GO

*/

USE [master]

GO

GRANT SELECT ON sys.dm_os_hosts TO [theUser]

GRANT SELECT ON sys.dm_os_memory_brokers TO [theUser]

GRANT SELECT ON sys.dm_os_memory_allocations TO [theUser]

GRANT SELECT ON sys.dm_db_mirroring_past_actions TO [theUser]

GRANT SELECT ON sys.dm_xe_session_object_columns TO [theUser]

GRANT SELECT ON sys.dm_os_loaded_modules TO [theUser]

GRANT SELECT ON sys.dm_db_task_space_usage TO [theUser]

GRANT SELECT ON sys.dm_os_memory_objects TO [theUser]

GRANT SELECT ON sys.dm_audit_class_type_map TO [theUser]

GRANT SELECT ON sys.dm_os_schedulers TO [theUser]

GRANT SELECT ON sys.dm_os_server_diagnostics_log_configurations TO [theUser]

GRANT SELECT ON sys.dm_hadr_instance_node_map TO [theUser]

GRANT SELECT ON sys.dm_os_dispatcher_pools TO [theUser]

GRANT SELECT ON sys.dm_os_threads TO [theUser]

GRANT SELECT ON sys.dm_exec_requests TO [theUser]

GRANT SELECT ON sys.dm_tran_commit_table TO [theUser]

GRANT SELECT ON sys.dm_fts_outstanding_batches TO [theUser]

GRANT SELECT ON sys.dm_repl_tranhash TO [theUser]

GRANT SELECT ON sys.dm_hadr_cluster TO [theUser]

GRANT SELECT ON sys.dm_qn_subscriptions TO [theUser]

GRANT SELECT ON sys.dm_db_session_space_usage TO [theUser]

GRANT SELECT ON sys.dm_exec_query_optimizer_info TO [theUser]

GRANT SELECT ON sys.dm_xe_map_values TO [theUser]

GRANT SELECT ON sys.dm_tran_top_version_generators TO [theUser]

GRANT SELECT ON sys.dm_fts_fdhosts TO [theUser]

GRANT SELECT ON sys.dm_xe_sessions TO [theUser]

GRANT SELECT ON sys.dm_db_log_space_usage TO [theUser]

GRANT SELECT ON sys.dm_hadr_name_id_map TO [theUser]

GRANT SELECT ON sys.dm_os_waiting_tasks TO [theUser]

GRANT SELECT ON sys.dm_exec_background_job_queue TO [theUser]

GRANT SELECT ON sys.dm_db_missing_index_details TO [theUser]

GRANT SELECT ON sys.dm_clr_properties TO [theUser]

GRANT SELECT ON sys.dm_os_sublatches TO [theUser]

GRANT SELECT ON sys.dm_exec_query_memory_grants TO [theUser]

GRANT SELECT ON sys.dm_logpool_hashentries TO [theUser]

GRANT SELECT ON sys.dm_tran_current_snapshot TO [theUser]

GRANT SELECT ON sys.dm_os_wait_stats TO [theUser]

GRANT SELECT ON sys.dm_os_memory_node_access_stats TO [theUser]

GRANT SELECT ON sys.dm_os_spinlock_stats TO [theUser]

GRANT SELECT ON sys.dm_database_encryption_keys TO [theUser]

GRANT SELECT ON sys.dm_hadr_availability_replica_states TO [theUser]

GRANT SELECT ON sys.dm_broker_connections TO [theUser]

GRANT SELECT ON sys.dm_db_mirroring_auto_page_repair TO [theUser]

GRANT SELECT ON sys.dm_server_registry TO [theUser]

GRANT SELECT ON sys.dm_os_dispatchers TO [theUser]

GRANT SELECT ON sys.dm_os_stacks TO [theUser]

GRANT SELECT ON sys.dm_filestream_non_transacted_handles TO [theUser]

GRANT SELECT ON sys.dm_xe_session_targets TO [theUser]

GRANT SELECT ON sys.dm_audit_actions TO [theUser]

GRANT SELECT ON sys.dm_hadr_availability_group_states TO [theUser]

GRANT SELECT ON sys.dm_os_ring_buffers TO [theUser]

GRANT SELECT ON sys.dm_db_missing_index_groups TO [theUser]

GRANT SELECT ON sys.dm_hadr_cluster_members TO [theUser]

GRANT SELECT ON sys.dm_db_uncontained_entities TO [theUser]

GRANT SELECT ON sys.dm_exec_cached_plans TO [theUser]

GRANT SELECT ON sys.dm_hadr_availability_replica_cluster_states TO [theUser]

GRANT SELECT ON sys.dm_exec_sessions TO [theUser]

GRANT SELECT ON sys.dm_broker_forwarded_messages TO [theUser]

GRANT SELECT ON sys.dm_resource_governor_resource_pools TO [theUser]

GRANT SELECT ON sys.dm_os_memory_clerks TO [theUser]

GRANT SELECT ON sys.dm_hadr_auto_page_repair TO [theUser]

GRANT SELECT ON sys.dm_repl_articles TO [theUser]

GRANT SELECT ON sys.dm_xe_session_events TO [theUser]

GRANT SELECT ON sys.dm_fts_memory_buffers TO [theUser]

GRANT SELECT ON sys.dm_fts_index_population TO [theUser]

GRANT SELECT ON sys.dm_tran_current_transaction TO [theUser]

GRANT SELECT ON sys.dm_os_cluster_properties TO [theUser]

GRANT SELECT ON sys.dm_os_child_instances TO [theUser]

GRANT SELECT ON sys.dm_exec_connections TO [theUser]

GRANT SELECT ON sys.dm_server_memory_dumps TO [theUser]

GRANT SELECT ON sys.dm_exec_background_job_queue_stats TO [theUser]

GRANT SELECT ON sys.dm_os_memory_broker_clerks TO [theUser]

GRANT SELECT ON sys.dm_filestream_file_io_handles TO [theUser]

GRANT SELECT ON sys.dm_hadr_availability_replica_cluster_nodes TO [theUser]

GRANT SELECT ON sys.dm_fts_active_catalogs TO [theUser]

GRANT SELECT ON sys.dm_tran_database_transactions TO [theUser]

GRANT SELECT ON sys.dm_filestream_file_io_requests TO [theUser]

GRANT SELECT ON sys.dm_cdc_log_scan_sessions TO [theUser]

GRANT SELECT ON sys.dm_os_memory_cache_clock_hands TO [theUser]

GRANT SELECT ON sys.dm_repl_schemas TO [theUser]

GRANT SELECT ON sys.dm_db_mirroring_connections TO [theUser]

GRANT SELECT ON sys.dm_db_partition_stats TO [theUser]

GRANT SELECT ON sys.dm_os_sys_memory TO [theUser]

GRANT SELECT ON sys.dm_io_pending_io_requests TO [theUser]

GRANT SELECT ON sys.dm_hadr_cluster_networks TO [theUser]

GRANT SELECT ON sys.dm_os_nodes TO [theUser]

GRANT SELECT ON sys.dm_tcp_listener_states TO [theUser]

GRANT SELECT ON sys.dm_os_memory_cache_entries TO [theUser]

GRANT SELECT ON sys.dm_os_virtual_address_dump TO [theUser]

GRANT SELECT ON sys.dm_cryptographic_provider_properties TO [theUser]

GRANT SELECT ON sys.dm_tran_transactions_snapshot TO [theUser]

GRANT SELECT ON sys.dm_os_memory_cache_hash_tables TO [theUser]

GRANT SELECT ON sys.dm_cdc_errors TO [theUser]

GRANT SELECT ON sys.dm_resource_governor_configuration TO [theUser]

GRANT SELECT ON sys.dm_exec_query_stats TO [theUser]

GRANT SELECT ON sys.dm_fts_semantic_similarity_population TO [theUser]

GRANT SELECT ON sys.dm_clr_tasks TO [theUser]

GRANT SELECT ON sys.dm_os_worker_local_storage TO [theUser]

GRANT SELECT ON sys.dm_db_persisted_sku_features TO [theUser]

GRANT SELECT ON sys.dm_db_index_usage_stats TO [theUser]

GRANT SELECT ON sys.dm_os_buffer_descriptors TO [theUser]

GRANT SELECT ON sys.dm_tran_active_snapshot_database_transactions TO [theUser]

GRANT SELECT ON sys.dm_server_services TO [theUser]

GRANT SELECT ON sys.dm_tran_active_transactions TO [theUser]

GRANT SELECT ON sys.dm_db_file_space_usage TO [theUser]

GRANT SELECT ON sys.dm_broker_activated_tasks TO [theUser]

GRANT SELECT ON sys.dm_broker_queue_monitors TO [theUser]

GRANT SELECT ON sys.dm_os_memory_cache_counters TO [theUser]

GRANT SELECT ON sys.dm_tran_session_transactions TO [theUser]

GRANT SELECT ON sys.dm_clr_appdomains TO [theUser]

GRANT SELECT ON sys.dm_exec_trigger_stats TO [theUser]

GRANT SELECT ON sys.dm_os_memory_pools TO [theUser]

GRANT SELECT ON sys.dm_os_latch_stats TO [theUser]

GRANT SELECT ON sys.dm_io_backup_tapes TO [theUser]

GRANT SELECT ON sys.dm_resource_governor_workload_groups TO [theUser]

GRANT SELECT ON sys.dm_hadr_database_replica_states TO [theUser]

GRANT SELECT ON sys.dm_fts_memory_pools TO [theUser]

GRANT SELECT ON sys.dm_resource_governor_resource_pool_affinity TO [theUser]

GRANT SELECT ON sys.dm_os_sys_info TO [theUser]

GRANT SELECT ON sys.dm_tran_locks TO [theUser]

GRANT SELECT ON sys.dm_exec_procedure_stats TO [theUser]

GRANT SELECT ON sys.dm_hadr_database_replica_cluster_states TO [theUser]

GRANT SELECT ON sys.dm_exec_query_transformation_stats TO [theUser]

GRANT SELECT ON sys.dm_exec_query_resource_semaphores TO [theUser]

GRANT SELECT ON sys.dm_repl_traninfo TO [theUser]

GRANT SELECT ON sys.dm_db_missing_index_group_stats TO [theUser]

GRANT SELECT ON sys.dm_fts_population_ranges TO [theUser]

GRANT SELECT ON sys.dm_os_performance_counters TO [theUser]

GRANT SELECT ON sys.dm_os_workers TO [theUser]

GRANT SELECT ON sys.dm_xe_session_event_actions TO [theUser]

GRANT SELECT ON sys.dm_db_script_level TO [theUser]

GRANT SELECT ON sys.dm_server_audit_status TO [theUser]

GRANT SELECT ON sys.dm_io_cluster_shared_drives TO [theUser]

GRANT SELECT ON sys.dm_os_tasks TO [theUser]

GRANT SELECT ON sys.dm_db_fts_index_physical_stats TO [theUser]

GRANT SELECT ON sys.dm_xe_packages TO [theUser]

GRANT SELECT ON sys.dm_logpool_stats TO [theUser]

GRANT SELECT ON sys.dm_os_memory_nodes TO [theUser]

GRANT SELECT ON sys.dm_tran_version_store TO [theUser]

GRANT SELECT ON sys.dm_os_windows_info TO [theUser]

GRANT SELECT ON sys.dm_os_cluster_nodes TO [theUser]

GRANT SELECT ON sys.dm_os_process_memory TO [theUser]

GRANT SELECT ON sys.dm_xe_objects TO [theUser]

GRANT SELECT ON sys.dm_xe_object_columns TO [theUser]

GRANT SELECT ON sys.dm_clr_loaded_assemblies TO [theUser]

GO

GRANT VIEW SERVER STATE TO [theUser]

GO

GRANT VIEW DATABASE STATE TO [theUser]

GO

Performance related trace-flags for SQL Server 2005 & 2008

I mostly wanted to post this in order to record it for later reference.
If you're interested in these flags, please proceed to the MS Support site using the following link.

https://support.microsoft.com/en-us/kb/920093?sd=rss&spid=16139
 

Chat doesn't work in WOT for XBox One, but works elsewhere

I've recently had an issue with my chat not working in WOT for XB1, but working everywhere else.
To resolve, I had to do a hard restart on the console.
Hold the power-button down for quite some time (about 10 seconds) to power down the console entirely.
When it came back up, chat worked.

Good luck!

Netbeans Issues with tableChanged event for JTable

I've struggled most of the day today to get the tableChanged() event to fire for a JTable I created to link to a database table, and wanted to share my solution.

As the linked video shows, after creating a custom listener that implements TableModelListener, and adding the listener to the table model object, the listener refused to fire.

Long story short, the answer turned out to be that I had failed to provide a name for the Default elements binding object. (See screenshot immediately below)

Here is a video of me demonstrating this phenomenon more thoroughly. I hope you find it instructive.

SQL Server: T-SQL JOB to kill sessions with long-running transactions

We have a situation in a build/test server in which the application server (jboss, in this case) is unceremoniously killed by the build process.

This is leaving us with transactions that are not committed, nor are they rolled back. I like to call these zombies. SQL Server thinks they're just happily executing.

Because SQL Server has no server side lock, transaction, or query timeout, I developed this job to simulate a 10 minute transaction timeout.

It's not sophisticated, but it will give you a good starting point in case you should have to create your own .

if not exists (select * from sys.schemas where name = 'LOGSCHEMA')
EXEC ('CREATE SCHEMA [LOGSCHEMA] AUTHORIZATION [dbo]')
GO

if exists (select * from sys.tables where name = '#OLD_SPIDS')
EXEC ('DROP TABLE #OLD_SPIDS')
GO

 /* NOTE: In the following, I fully qualified the Kill_Long_Running_tran_log table as dbname.schema.tablename. DB_NAME will need to be changed */

if not exists (select * from sys.tables where name = 'Kill_Long_Running_tran_Log')
EXEC ('CREATE TABLE [/*change db_name */DB_NAME].[LOGSCHEMA].[Kill_Long_Running_tran_Log] (msg_timestamp datetime2(7), msg varchar(256))')

DECLARE @session_to_kill int = 0
DECLARE @kill_session_statement nvarchar(max)

select dtst.session_id
, dtst.transaction_id
, dtat.name
, dtat.transaction_begin_time
INTO #OLD_SPIDS
from sys.dm_tran_session_transactions dtst
join sys.dm_tran_active_transactions dtat
on dtst.transaction_id = dtat.transaction_id
where dtat.transaction_begin_time < DATEADD(MINUTE,-10,current_timestamp)

select @session_to_kill = session_id
from #OLD_SPIDS
where transaction_begin_time = (select min(transaction_begin_time) from #OLD_SPIDS )

select @kill_session_statement = 'kill ' + cast(@session_to_kill as varchar(10))

if (@session_to_kill != 0)
begin
insert into [LOGSCHEMA].[Kill_Long_Running_tran_Log]
values (current_timestamp, @kill_session_statement)
exec (@kill_session_statement)
end 

DROP TABLE #OLD_SPIDS

Windows - Cannot remove empty file

Encountered a weird one, today. Unfortunately, I did not get screen-shots.
When trying to delete a folder, I got a message that I could not because a sub-folder was open in another program.
Tried from command line, and it said that the folder was not empty.

Hmmm. That's odd ;-)

So I went folder by folder up the chain and found one that was marked as read-only.  I changed that setting, and was able to delete my folders.

Hope this help someone. Sorry for the lack of screen-shots.

ERROR: ORA-01033: ORACLE initialization or shutdown in progress

I recently ran across this error, which led me to this article in DBForums.
Unfortunately, it's been a while and I can't figure out my login, so I'm posting this here.

In my case, it was very definitely a user error :(
This was Oracle 12c, local development database on my laptop.
Because of space constraints, I had moved the USER tablespace to an external drive, and then forgot about it!

Oracle very happily started up and gave no visible objection, but when I tried to connect, it have this message.

So, long story short, if you get this, check that you can connect to all of your Oracle data files!

Happy databasing ;-)

MOVING ORACLE DATA FILES (Addendum)

There is a very good article on moving Oracle data files to be found here: https://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles005.htm

Unfortunately, it does no include a final step to bring the  tablespace back online.
The command is simple:

ALTER TABLESPACE USERS
 ONLINE;

So, execute the procedure in the link above, and when finished, make sure you bring your table-space back online.

IBM DATA STUDIO DEBUGGER OUT OF SYNC WITH SOURCE

I struggled most of the morning with my IBM Data Studio 4.1 debugger being out of sync with my source as I stepped through it.

The answer seems to be that I was creating string variables that had newlines in them.

Example:

SET PPN_INSERT_STRING = 'INSERT INTO FEDICC.MIG_PARTY_PERSON_NAME (
                   PARTY_PERS_NM_ID,
                   PARTY_ID,
                   PERS_NM_TTL_CD,
                   PERS_NM_SUFX_CD,
                   PERS_NM_TYP_CD,
                   PARTY_PERS_NM_NM_LST,
                   PARTY_PERS_NM_NM_FST,
                   PARTY_PERS_NM_NM_MID,
                   PARTY_PERS_NM_NM_UPCS_SRCH,
                   PARTY_PERS_NM_NM_DISP_DRV,
                   PARTY_PERS_NM_NM_LST_UPCS_SRCH,
                   PARTY_PERS_NM_NM_FST_UPCS_SRCH,
                   PARTY_PERS_NM_NM_MID_UPCS_SRCH,
                   PARTY_PERS_NM_NM_LST_DUP_SRCH,
                   PARTY_PERS_NM_NM_FST_DUP_SRCH,
                   PARTY_PERS_NM_NM_MID_DUP_SRCH,
                   EXTRNL_SYS_ID_VAL_TXT,
                   PARTY_PERS_NM_EFF_DT,
                   PARTY_PERS_NM_END_DT,
                   CNCURNCY_ID,
                   AUDT_USER_CREA_ID,
                   AUDT_USER_CREA_DTM,
                   AUDT_USER_UPDT_ID,
                   AUDT_USER_UPDT_DTM,
                   DEL_IND) VALUES ';

I changed this to be all on one line, and the debugger is no synced with the source.

There must be some defect by which the debugger cannot properly handle statements like the one above.

This works: (Using screen shot since it will just paginate on the blog):

To achieve this, I used Notepad++ with a regular expression search, replacing \r\n with a space, and then replaceing * with a single space.

Happy hunting :)

Leadership

I think that this might be the best, most succinct article on leadership I've ever read.

You may have to create a login to read it, but I've found SQLServer Central to be a great resource for the SQL Server DBA, so it's well worth your time.

Leadership and the Data Professional

http://www.sqlservercentral.com/blogs/the-sql-professor/2016/02/10/leadership-and-the-data-professional/