Unfortunately there is no way to use SQL (that I know of) to ask DB2 for all its databases. Perhaps this is a difference between how DB2 is set up/managed versus other databases. A DB2 instance is essentially a database server. It does not do SQL per se as it is not a database. It is the "brains" or "guts" that runs the DB2 logic (and yes, it thus runs SQL, but it is not a database in and of itself). You can create several databases within an instance. But those databases are not stored in a database. Their information is stored in the local database directory, system database directory, and (if remote) the node directory. (Well, and if remote and z/OS or i5/OS, there is also the DCS directory.) This is because the DB2 instance can know about both local and remote databases.

Because of this you have to ask the instance to check its directories for what databases it knows about. This is where you use the non-SQL statements like above.

db2 list db directory will give you the system directory, ie, all databases both local and remote.
db2 list db directory on <path> will give you the local database directory, where local databases reside on disk
db2 list node directory will give you the nodes (ie, the remote servers) that remote databases reside on.
db2 list dcs directory will give you the DCS entries needed for connecting to remote z/OS and i5/OS systems/databases.

So, you unfortunately can't use a database driver as there is nothing to query from database-wise. When you are talking to an instance you aren't necessarily connected to a database. And if you are connected to a database, it won't have information about the other databases, even in its catalog tables, as those are only for the particular database you are connected to.

Now to solve your issue, you could still do this programmatically. You could open a command line prompt (through your code) and if your prompt has DB2 set up in its path (so it can execute the DB2 commands) you could then issue these commands through your code that way. In my past life as a Java developer, I did this with working with some things with DB2 (like dynamically setting certain configuration parameters).


Unfortunately you need to migrate your 2005 packages to 2012 using Data Tools. Depending on the complexity of each one this task will be more or less complicated. Here some useful links that I have used in the past:

https://devinknightsql.com/2012/05/16/upgrading-packages-to-ssis-2012/

https://sqldusty.com/2012/03/19/upgrade-2008-ssis-packages-to-2012-like-a-boss/

https://blogs.msdn.microsoft.com/mattm/2008/03/22/package-upgrade-wizard/


What you want to do is something like this (all the code below is available on the fiddle here):
CREATE TABLE work_calendar
(
  the_day    DATE NOT NULL PRIMARY KEY,
  day_name   TEXT NOT NULL,
  start_time TIME(0) NULL,
  end_time   TIME(0) NULL
);

I also did this:
CREATE INDEX st_ix ON work_calendar (start_time);  -- these indexes reduce the execution time
CREATE INDEX et_ix ON work_calendar (end_time); -- run EXPLAIN (ANALYZE, BUFFERS)

You can experiment with EXPLAIN (ANALYZE, BUFFERS) and performance on your own H/W and S/W setup - see the fiddle.
and for bank_holidays - some of which are variable (Easter for example):
CREATE TABLE bank_holiday
(
  the_day TEXT NOT NULL,
  bh_date DATE NOT NULL
);

INSERT INTO bank_holiday
VALUES
('New Year''s Day',    '2021-01-01'::DATE),
('St. Patrick''s Day', '2021-03-17'::DATE),  -- Irish feast day
('Easter Monday',      '2021-05-04'::DATE),
('May Day',            '2021-05-01'::DATE),
('Christmas Day',      '2021-12-25'::DATE),
('St. Stephen''s Day', '2021-12-26'::DATE);

And then you run the following SQL:
-- EXPLAIN (ANALYZE, BUFFERS) -- check with different indexing strategies.
WITH t (opening_day) AS
(
  SELECT  GENERATE_SERIES
  (
    '2021-01-01'::DATE,
    '2021-12-31'::DATE,
    '1 DAY'
  ) AS ds
)
INSERT INTO work_calendar
SELECT
  opening_day,
  TO_CHAR(opening_day, 'Day'),

  CASE

    -- Set start time = '00:00:00' for bank holidays and Sundays

    WHEN (opening_day IN (SELECT bh_date FROM bank_holiday))
      OR EXTRACT(DOW FROM opening_day) = 0 THEN '00:00:00'::TIME

   -- Ater this WHEN, the CASE statement is over - it's like BREAK; in C (or JAVA...)
   -- the code drops out of the CASE statement.

   -- So, now, we tackle Saturdays and the Christmas period dates:

   -- set start time = 10:00:00 for Saturdays that are not bank holidays and
   -- set start time = 10:00:00 for days from Christmas Eve to New Year's Day

    WHEN EXTRACT(DOW FROM opening_day) = 6
      OR (opening_day >= '2021-12-24' AND opening_day <= '2021-12-31') THEN '10:00:00'::TIME

    -- Now, we deal with the rest - i.e. Mondays to Fridays of those days which are
    -- not Bank Holidays or in the Christmas period.

    WHEN EXTRACT (DOW FROM opening_day) BETWEEN 1 AND 5 THEN '09:00'::TIME

    ELSE NULL

  END AS ot,

  CASE

    -- Set end time = '00:00:00' for Bank Holidays and Sundays

    WHEN (opening_day IN (SELECT bh_date FROM bank_holiday))
      OR EXTRACT(DOW FROM opening_day) = 0 THEN '00:00:00'::TIME

   -- Ater this WHEN, the CASE statement is over - it's like BREAK; in C (or JAVA...)
   -- the code drops out of the CASE statement.

   -- So, now, we tackle the Christmas period dates: - the Christmas period end time
   -- is 13:00 and not 15:00 - i.e. it's not (unlike for start time) the normal
   -- Saturday end time - so we need an extra WHEN in the CASE

   -- set end time =   13:00:00 for Saturdays that are not bank holidays and
   -- set start time = 10:00:00 for days from Christmas Eve to New Year's Day

      WHEN opening_day >= '2021-12-24' AND opening_day <= '2021-12-31'
        THEN '13:00:00'::TIME

    -- Now, we deal with normal Saturdays which are not Bank Holidays or which
    -- don't fall in the Christmas period.

      WHEN EXTRACT(DOW FROM opening_day) = 6 THEN '15:00:00'::TIME

    -- Finally, we have the normal working day - end time is 18:00

      WHEN EXTRACT (DOW FROM opening_day) BETWEEN 1 AND 5 THEN '18:00'::TIME

    ELSE NULL

  END AS ft
FROM t;

And to check the result, I had this query:
SELECT * FROM work_calendar
WHERE the_day >= '2021-01-01' AND the_day <= '2021-01-13'
OR    the_day >= '2021-03-13' AND the_day <= '2021-03-24'  -- St. Patrick's day Bank Holiday
OR    the_day >= '2021-04-01' AND the_day <= '2021-04-10'  -- Easter Monday
OR    the_day >= '2021-04-28' AND the_day <= '2021-05-05'  -- May Day
OR    the_day >= '2021-12-20' AND the_day <= '2021-12-31'  -- Christmas period
ORDER BY the_day;

The idea behind this is to verify that the SQL is doing what I hope it's doing - so I check the "edge" cases - i.e. beginning of year, end of year, around Bank Holidays and over the Christmas period to New Year's Eve.
There are 55 records in the result set - I'll only show those around the New Year and the Christmas period:
the_day     day_name    start_time  end_time
2021-01-01  Friday      00:00:00    00:00:00  -- BH - no work
2021-01-02  Saturday    10:00:00    15:00:00  -- Sat. st 10:00, et 15:00
2021-01-03  Sunday      00:00:00    00:00:00  -- Sun. day off - so far, so good
2021-01-04  Monday      09:00:00    18:00:00  -- Normal work resumes
2021-01-05  Tuesday     09:00:00    18:00:00  --        "
...
... gap  -- inspection shows that these are all OK
...
2021-12-20  Monday      09:00:00    18:00:00  -- Normal working day
2021-12-21  Tuesday     09:00:00    18:00:00  --        "
2021-12-22  Wednesday   09:00:00    18:00:00  --        "
2021-12-23  Thursday    09:00:00    18:00:00  --        "
2021-12-24  Friday      10:00:00    13:00:00  -- Christmas Eve - start of Christmas period 10 - 13
2021-12-25  Saturday    00:00:00    00:00:00  -- Christmas Day - BH, no work!
2021-12-26  Sunday      00:00:00    00:00:00  -- Sunday + St. Stephen's day - no work
2021-12-27  Monday      10:00:00    13:00:00  -- Christmas period working
2021-12-28  Tuesday     10:00:00    13:00:00  --          "
2021-12-29  Wednesday   10:00:00    13:00:00  --          "
2021-12-30  Thursday    10:00:00    13:00:00  --          "
2021-12-31  Friday      10:00:00    13:00:00  --          "
55 rows

A further check:
SELECT * FROM work_calendar WHERE start_time IS NULL;

returns no records - as we would expect!
So, we can see that we have the appropriate hours for the appropriate days - i.e. weekday, 09:00 - 18:00, Saturday, 10:00 to 15:00 and nothing on Sunday. Bank Holidays and the Christmas period are also catered for. Obviously, you will choose Bank Holidays for Germany/Saxony.
Re. performance. I would encourage you to check your own system for performance - but if it's only a year, then I don't imagine that it's going to be a huge problem - but it's always worth bearing in mind as good practice.


I resolved this issue by setting this attribute

BEGIN
  DBMS_SCHEDULER.set_scheduler_attribute (
    attribute => 'default_timezone',
    value     => 'EST');
END;

and moving the job to DBMS_SCHEDULER.  Now, when it runs the correct (local) time is entered in the logging table.


In each of Jobs 1, 2, and 3, add a second step. (Or a step after all of the other steps, if these are already multi-step jobs.)  Have that new step be:

exec sp_start_job 'Job 4'

And because you mention that you only want Job 4 to be started if the other jobs succeed, you should also adjust the Advanced properties of the existing final step in each of Jobs 1, 2, and 3 to be:

On success action: Go to the next step
On failure action: Quit the job reporting failure


In general when running queries (SQL Statements) against a database you want all of the statements to successfully completed and if they do not revert data to what is was before you ran the sql statement. The overall concept you are looking for is ACID

In order to provide this functionality, Databases generally support the concept of a transaction.

When you start a SQL Statement with a Transaction you are telling the database that all statements within the transaction must complete successfully and if they do not the database will rollback(revert) your changes putting the database back to the way it was before you made a change.

Take the following two statements:

Insert INTO UserName VALUES (123,'Test User')
Insert INTO UserCommission VALUES (123,0.5)

If you run the above statements without a transaction and the first statement failed then the second statement will still run thus inserting a row into the UserCommission table without an associated row in the UserName table.

If you then run the following

BEGIN TRANSACTION
Insert INTO UserName VALUES (123,'Test User')
Insert INTO UserCommission VALUES (123,0.5)
IF NO Errors Commit ELSE Rollback

In this scenario if the first statement failed you can check for errors and then rollback the transaction (sql statements) leaving the database as though you had never run the statement so you no longer have a row in the UserCmomission table without a corresponding row in the UserName table.

In general you only need a transaction when you are actually changing the data such as Insert, Delete, Update etc but sometimes as part of procedures you may have select statement to get some data that maybe use in Insert, Delete, Update etc statements.

Locks on the other had deal with how the database actually internally marks that the data is in use so that if other user are querying the same data the database knows that the data is in use.

Depending on how your table is constructed (keys, indexes etc), the size of the table etc and the type of action you are performing ( Select, Insert, Update, Delete) the database will perform many different types of locks such as page, table, row locks etc

Of course all of these can impact the performance of your queries and/or database.


what is a safe way to stop the logreader?

You can stop the log reader agent job in sql server agent. It is used by publications that are involved in t-rep. It basically copies the transactions marked for replication from the transaction log into the distribution database. Should be safe to stop it.


Does DB2 support host based access control?

By default DB2 delegates all authentication to the operating system of the server, so the "host" is always the same -- the DB2 server host and the authenticated user ID does have the "host" component and all privileges are verified against the user ID only.

Depending on what you are actually trying to achieve, you may be able to restrict access to certain actions or objects via custom authentication plugin, workload manager controls, or row/column access control mechanisms.

Edit

Thanks to @IanBjorhovde for reminding us of yet another option: trusted contexts (see tutorial), although it still is not an exact match. Even though you can restrict clients with certain IPs from connecting to the database, authentication is still performed at the server, so it's not exactly "user@remote_host" but rather "user@server_host connecting from remote_host".


mongodb replication node stuck at “STARTUP2” with optimeDate as 1970

No, this is not OK, STARTUP2 should only be a state that a secondary is in briefly on its way to a full sync and SECONDARY status (via RECOVERING usually) - see the states table for more.  However, without seeing log files it's impossible to say why it is stuck.  The 1970 date you are seeing in the optime is basically the epoch/Unix time version of zero, indicating that it has not applied any ops.

The basic method for restoring this secondary would be to shut it down, wipe out its data files (all files in the dbpath), and restart it.  That will restart the initial sync process and it should get to SECONDARY.  If it gets stuck again, then look at the logs for more information as to why it is happening - the most common causes would be some sort of resource issue or possibly not being able to talk to the primary in the set reliably.


Is it good to create new indexes for better performance - SQL Server

I always take careful consideration when creating indexes on production systems, always try to create in DEV first. There are several reasons, but the top three for me are. First, there is a cost associated to indexes, essentially more overhead, so depending on your table the cost can be quite high. Second, I am never always privy to all the functionality that could be accessing that table/dataset, so I have seen negative results in other parts of an application due to adding indexes for other reasons. Third, if this is a data warehouse or a table that experiences frequent writes/loads, then you may actually negatively affect performance (unless you drop and add the indexes before and after).


My SQL syntax is error, how to solve it?

MySQL doesn't support Common Table Expressions (CTE) so either your mysql tag is incorrect or you've pulled this syntax from nowhere.

To make this work with MySQL, you'll need to rewrite it as derived tables. Note, these sql fiddles use the question that was a duplicate of this as it was at least more interesting to answer.https://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql

This is the SQL Server version but I guarantee whatever logic you're attempting could be simplified if you explained what you were attempting to do versus "code doesn't work"

This specific question suffers from two issues: The first and already addressed is that CTEs WITH don't exist in mysql.

The second is simply syntax. You have SELECTMAX instead of SELECT MAX

WITH max_budget(value) AS (SELECT MAX(budget) FROM department)
SELECT budget
FROM department, max_budget
WHERE department.budget = max_budget.value;

The above is valid syntax for SQL Server and all DBMS that have implemented CTEs. The MySQL equivalent follows

SELECT
    *
FROM
    department
WHERE
    department.budget =
    (
        SELECT MAX(budget) FROM department
    );

This uses a correlated subquery which has the distinct advantage of working in SQL Server, MySQL, PostGres, etc.

You could also write it as a derived table:

SELECT
    department.*
FROM
    department
  JOIN
    (
        SELECT MAX(budget) AS value FROM department
    )  max_budget
  ON
    department.budget = max_budget.value ;


MySQL: split value in column to get multiple rows

What you are looking for is the inverse of a GROUP BY aggregate query using the GROUP_CONCAT. If you are willing to store the results in a temp table, I got just the thing.

First, here is the code to use you sample data in a table called prod and a temp table called prodcat to hold the results you are looking for.

use test
drop table if exists prod;
drop table if exists prodcat;
create table prod
(
  product_id int not null,
  categories varchar(255)
) engine=MyISAM;
create table prodcat
(
  product_id int not null,
  cat  int not null
) engine=MyISAM;
insert into prod values
(10,'9,12'),(11,'8'),(12,'11,18,5');
select * from prod;

Here it is loaded

mysql> use test
Database changed
mysql> drop table if exists prod;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists prodcat;
Query OK, 0 rows affected (0.00 sec)

mysql> create table prod
    -> (
    ->   product_id int not null,
    ->   categories varchar(255)
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.07 sec)

mysql> create table prodcat
    -> (
    ->   product_id int not null,
    ->   cat  int not null
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into prod values
    -> (10,'9,12'),(11,'8'),(12,'11,18,5');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from prod;
+------------+------------+
| product_id | categories |
+------------+------------+
|         10 | 9,12       |
|         11 | 8          |
|         12 | 11,18,5    |
+------------+------------+
3 rows in set (0.00 sec)

mysql>

OK, you need query to put together each product_id with each category. Here it is:

select concat('insert into prodcat select ',product_id,',cat from (select NULL cat union select ',
replace(categories,',',' union select '),') A where cat IS NOT NULL;') ProdCatQueries from prod;

Here it is executed

mysql> select concat('insert into prodcat select ',product_id,',cat from (select NULL cat union select ',
    -> replace(categories,',',' union select '),') A where cat IS NOT NULL;') ProdCatQueries from prod;
+----------------------------------------------------------------------------------------------------------------------------------+
| ProdCatQueries                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------+
| insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL;                 |
| insert into prodcat select 11,cat from (select NULL cat union select 8) A where cat IS NOT NULL;                                 |
| insert into prodcat select 12,cat from (select NULL cat union select 11 union select 18 union select 5) A where cat IS NOT NULL; |
+----------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>

Let me run each line by hand

mysql> insert into prodcat select 10,cat from (select NULL cat union select 9 union select 12) A where cat IS NOT NULL;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into prodcat select 11,cat from (select NULL cat union select 8) A where cat IS NOT NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into prodcat select 12,cat from (select NULL cat union select 11 union select 18 union select 5) A where cat IS NOT NULL;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql>

OK, good. The queries work. Did the prodcat table populate properly?

mysql> select * from prodcat;
+------------+-----+
| product_id | cat |
+------------+-----+
|         10 |   9 |
|         10 |  12 |
|         11 |   8 |
|         12 |  11 |
|         12 |  18 |
|         12 |   5 |
+------------+-----+
6 rows in set (0.00 sec)

mysql>

OK Great. It has the data.

To be honest, I think SQL Server can perform all of this in a single pivot query without a handmade temp table.

I could have taken it to another level and concatenated all the queries into a single query, but the SQL would have been insanely long. If your actual query had 1000s of rows, a single MySQL would not have been practical.

Instead of running the 3 INSERT queries by hand, you could echo the 3 INSERT queries to a text file and execute it as a script. Then, you have a table with the products and categories combinations individually written.


Lot of log files in $FSLOG generating. How to stop?

Make a backup. Those are not log files - where did you get this idea from?

Yu basically store some data in the file system - MyDatabaseStream_Filegroup_Data\$FSLOG\

Database FIleStream feature. VERY nice if you (a) know what you do there and (b) need it.

Either you back up - thzen surplus can be deleted - or you move it to another disc or.... you disable file stream storage.


SQL USING CAST in a query inside a stored Procedure

You have data in approxstartdate column that cannot be converted to datetime, it’s bad data, chances are if you saw what that data is you wouldn’t know how to convert it to a datetime either.

If you are using SQL Server, take a look at try_convert which returns null if the value cannot be converted. You can use this function to find the values in your table that cannot be converted and then fix them or remove them.


adding 2nd catalog for linked server to DB2

From my experience there can be just 1 catalog per 1 linked server

You will need to create separate linked servers for each database on remote server (DB2) that you want to be available for querying on your "local" server (SQL Server)


Delete rows where matched value is twice as much

If you want to DELETE all the information in table1.*:

DELETE table1.*
FROM test.table1
JOIN test.table2 ON (table2.id = table1.id)
WHERE table1.price < (table2.price*2);

This will DELETE all rows from table1 that match with the WHERE criteria. If you want to DELETE all the rows that match against the WHERE criteria in table2, just replace DELETE table1.* to DELETE table2.*.

As the MySQL's documentation says:

Multi-Table Deletes

  You can specify multiple tables in a DELETE
  statement to delete rows from one or more tables depending on the
  condition in the WHERE clause. You cannot use ORDER BY or LIMIT in a
  multiple-table DELETE. The table_references clause lists the tables
  involved in the join, as described in Section 13.2.9.2, “JOIN Syntax”.

  For the first multiple-table syntax, only matching rows from the
  tables listed before the FROM clause are deleted. For the second
  multiple-table syntax, only matching rows from the tables listed in
  the FROM clause (before the USING clause) are deleted. The effect is
  that you can delete rows from many tables at the same time and have
  additional tables that are used only for searching:

 DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id
 AND t2.id=t3.id;

  Or:

 DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE
 t1.id=t2.id AND t2.id=t3.id;

  If you use a multiple-table DELETE statement involving InnoDB tables
  for which there are foreign key constraints, the MySQL optimizer might
  process tables in an order that differs from that of their
  parent/child relationship. In this case, the statement fails and rolls
  back. Instead, you should delete from a single table and rely on the
  ON DELETE capabilities that InnoDB provides to cause the other tables
  to be modified accordingly.

  Note If you declare an alias for a table, you must use the alias when
  referring to the table:

 DELETE t1 FROM test AS t1, test2 WHERE ...

  Table aliases in a multiple-table DELETE should be declared only in
  the table_references part of the statement. Elsewhere, alias
  references are permitted but not alias declarations.


Have a look at the DBMS_METADATA package. First set long to some big number so the output isn't truncated, and set the SQL terminator so you can see how statements are separated:

SQL> set long 30000
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

To get the tablespace (substitute your own for USERS of course):

SQL> select dbms_metadata.get_ddl('TABLESPACE', 'USERS') from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
--------------------------------------------------------------------------------

  CREATE TABLESPACE "USERS" DATAFILE
  '/home/oracle/app/oracle/oradata/orcl/users01.dbf' SIZE 5242880
  AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;
   ALTER DATABASE DATAFILE
  '/home/oracle/app/oracle/oradata/orcl/users01.dbf' RESIZE 256901120;

You'll probably need to modify the datafile path for the new server. To get the user:

SQL> select dbms_metadata.get_ddl('USER', 'SCOTT') from dual;

DBMS_METADATA.GET_DDL('USER','SCOTT')
--------------------------------------------------------------------------------

   CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:64F10A7102E73EFFAC1BE155794F1E8DA6C8F948A5713D5D5EC462AA0382;4DD35D67F0372586'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP";

Then you need the grants the user has. Depending on your actual user some of these may not return anything:

SQL> select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
--------------------------------------------------------------------------------

  GRANT CHANGE NOTIFICATION TO "SCOTT";
  GRANT DROP ANY DIRECTORY TO "SCOTT";
  GRANT CREATE ANY DIRECTORY TO "SCOTT";
  GRANT CREATE DATABASE LINK TO "SCOTT";
  GRANT CREATE VIEW TO "SCOTT";
  GRANT CREATE SYNONYM TO "SCOTT";
  GRANT CREATE TABLE TO "SCOTT";
  GRANT UNLIMITED TABLESPACE TO "SCOTT";
  GRANT ALTER SESSION TO "SCOTT";

SQL> select dbms_metadata.get_granted_ddl('ROLE_GRANT', 'SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','SCOTT')
--------------------------------------------------------------------------------

   GRANT "CONNECT" TO "SCOTT";
   GRANT "RESOURCE" TO "SCOTT";
   GRANT "XFILES_USER" TO "SCOTT";

SQL> select dbms_metadata.get_granted_ddl('OBJECT_GRANT', 'SCOTT') from dual;

DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT')
--------------------------------------------------------------------------------

  GRANT EXECUTE ON "SYS"."DBMS_LOCK" TO "SCOTT";
  GRANT EXECUTE ON "SYS"."DBMS_CRYPTO" TO "SCOTT";
  GRANT EXECUTE, READ, WRITE ON DIRECTORY "SO_DIR" TO "SCOTT";

You can use the same functions to get your table and view scripts if you prefer - I believe SQL Developer's export uses them under the hood anyway - and to get role definitions and grants.


You will probably need to enable log_slave_updates so the slave node generates binlog to be used on other nodes.

http://www.severalnines.com/blog/replicate-mysql-server-galera-cluster


Partition Key questions in SQL Server 2008

Assuming that you have the primary key on a clustered index then the partitioning key needs to be part of the primary key.

You will not loose the benefit of partitioning by joining to non-partitioned tables, providing that the queries are designed to make use of the partitioned table, for example the following query WILL benefit from partitioning

SELECT F.Col1, F.Col2, D.Col3
FROM Fact_Partitioned F
    INNER JOIN Dim_MyDim D ON F.Col1 = D.Col1
WHERE F.Col1 = 5

But the following query WILL NOT benefit from partition elimination

SELECT F.Col1, F.Col2, D.Col3
FROM Fact_Partitioned F
    INNER JOIN Dim_MyDim D ON F.Col1 = D.Col1
WHERE D.Col1 = 5

It is a subtle difference, but in the first query, the join key is filtered in the partitioned table, taking advantage of elimination and then joined to the dimension. In the second query, the key is filtered in the dimension and then joined against the whole of the fact table, rather than just required partitions.

It goes without saying that the partitioning key needs to be in the WHERE clause for elimination to work, otherwise SQL Server does not know which partition(s) the data is in.

Adding a filter criteria on the JOIN clause will not help you. It needs to be in the WHERE clause to benefit from elimination.

The Partition Key does not need to be part of a non-clustered index (NCI) but if the NCI is unique, then it needs to contain the partitioning key in order to align the index. This is where the NCI is built on the same partition scheme as the table.  NCIs should also be partition aligned unless there is an exceedingly good reason not to.  I have never come across a good enough reason!


How to get CPU usage by database for particular instance?

While I, like @Thomas, completely agree with @Aaron in the comments on the question regarding the concerns of "per-database CPU usage" being either accurate or useful, I can at least answer the question as to why those two queries are so different. And the reason why they are different will indicate which one is more accurate, though that higher level of accuracy is still relative to the one that is specifically inaccurate, hence still not truly accurate ;-).

The first query uses sys.dm_exec_query_stats to get CPU info (i.e. total_worker_time). If you go to the linked page that is the MSDN documentation for that DMV you will see a short, 3 sentence intro and 2 of those sentences give us most of what we need to understand the context of this info ("how reliable is it" and "how does it compare to sys.sysprocesses"). Those two sentences are:

  Returns aggregate performance statistics for cached query plans in SQL Server. ... When a plan is removed from the cache, the corresponding rows are eliminated from this view

The first sentence, "Returns aggregate performance statistics", tells us that the information in this DMV (just like several others) is cumulative and not specific to only queries that are currently running. This is also indicated by a field in that DMV that is not part of the query in the Question, execution_count, which again shows that this is cumulative data. And it is quite handy to have this data be cumulative as you can get averages, etc by dividing some of the metrics by the execution_count.

The second sentence, "plans being removed from the cache are also removed from this DMV", indicate that it is not a complete picture at all, especially if the server has a pretty full plan cache already and is under load and hence is expiring plans somewhat frequently. Also, most DMVs are reset when the server resets so they are not a true history even if these rows weren't removed when the plans expire.

Now let's contrast the above with sys.sysprocesses. This system view is only showing what is currently running, just like the combination of sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests (which is stated on the linked page for sys.dm_exec_sessions). This is an entirely different view of the server as compared to the sys.dm_exec_query_stats DMV which holds the data even after the process completes. Meaning, in relation to the "are the results from the second query wrong?" question, they are not wrong, they just pertain to a different aspect (i.e. time-frame) of performance stats.

So, the query using sys.sysprocesses is only looking at "right now". And the query using sys.dm_exec_query_stats is looking at mostly (maybe) what has happened since the last restart of the SQL Server service (or obviously system reboot). For general performance analysis it seems that sys.dm_exec_query_stats is far better, but again, it drops useful info all the time. And, in both cases, you also need to consider the points made by @Aaron in the question comments (since removed) regarding the accuracy of the "database_id" value in the first place (i.e. it only reflects the active DB that initiated the code, not necessarily where the "issue" is occurring).

But, if you just need/want to get a sense of what is happening right now across all Databases, possibly because things are slowing down right now, you are better off using the combination of sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests (and not the deprecated sys.sysprocesses). Just keep in mind that you are looking at/for queries, not databases, because queries can join across multiple databases, include UDFs from one or more databases, etc.

EDIT:
If the overall concern is reducing high CPU consumers, then look for the queries that are taking up the most CPU, because databases don't actually take up CPU (looking per database might work at a hosting company where each database is isolated and owned by a different customer).

The following query will help identify queries with high average CPU usage. It condenses the data in the query_stats DMV since those records can show the same query (yes, the same subset of the query batch) multiple times, each with a different execution plan.

;WITH cte AS
(
  SELECT stat.[sql_handle],
         stat.statement_start_offset,
         stat.statement_end_offset,
         COUNT(*) AS [NumExecutionPlans],
         SUM(stat.execution_count) AS [TotalExecutions],
         ((SUM(stat.total_logical_reads) * 1.0) / SUM(stat.execution_count)) AS [AvgLogicalReads],
         ((SUM(stat.total_worker_time) * 1.0) / SUM(stat.execution_count)) AS [AvgCPU]
  FROM sys.dm_exec_query_stats stat
  GROUP BY stat.[sql_handle], stat.statement_start_offset, stat.statement_end_offset
)
SELECT CONVERT(DECIMAL(15, 5), cte.AvgCPU) AS [AvgCPU],
       CONVERT(DECIMAL(15, 5), cte.AvgLogicalReads) AS [AvgLogicalReads],
       cte.NumExecutionPlans,
       cte.TotalExecutions,
       DB_NAME(txt.[dbid]) AS [DatabaseName],
       OBJECT_NAME(txt.objectid, txt.[dbid]) AS [ObjectName],
       SUBSTRING(txt.[text], (cte.statement_start_offset / 2) + 1,
       (
         (CASE cte.statement_end_offset
           WHEN -1 THEN DATALENGTH(txt.[text])
           ELSE cte.statement_end_offset
          END - cte.statement_start_offset) / 2
         ) + 1
       )
FROM cte
CROSS APPLY sys.dm_exec_sql_text(cte.[sql_handle]) txt
ORDER BY cte.AvgCPU DESC;


Why don't you try doing it from webmin. If you install the PostgreSQL module, under the postgres users section there is an option Add a new PostgreSQL user when a Unix user is added.


Assuming faculty_assets and faculty are joined by the fac_id field, you could use something like

$sql="DELETE FROM faculty WHERE fac_id='" . (int)$fac_id . "' AND NOT EXISTS
        (SELECT * FROM faculty_assets WHERE fac_id = '".(int)$fac_id . "')";


Data usage increases initially when reorganize partition mysql cluster

You're correct that the on-line repartitioning of data temporarily uses extra memory on the data nodes. In most cases the application data is split over many tables and so relatively little memory is needed when repartitioning each table. If the data is in one big table and you don't have the available RAM then there are a number of options...

Perform the data migration yourself in an iterative fashion. e.g.

create cdr2 (....) partition by ...;

loop
{
   insert into cdr2 select * from cdr order by .... LIMIT 1000;
   delete from cdr order by .... LIMIT 1000; } until cdr empty.

Some extra memory will be used, but shouldn't be double.  Best effect would be if the order by .... could follow insert-order in some way.
Backup the data and restore it on another Cluster (with more memory), repartition and then revers the process.
Use mysqldump, empty the table, repartition it and then load the data back in
Convert the table to be disk-based (on-line operation), repartition and then convert it back into an in-memory table
(Temporarily) add extra RAM to the data nodes (this is an on-line operation).

Regards, Andrew.


Algorithmic order of table indexes for table operations

Things are more complicated than that. Here are a few points of consideration.

First, this entire discussion assumes B-Trees or B+ Trees (Hence the o(log(n))). There are other types of indexes, like hash indexes, where access is in O(1). Your question insinuates you're looking up values using "equals" search (e.g. looking for X=17). But in this particular scenario, a Hash index is preferable, when possible.

I do agree though that most indexes you'll find today are B/B+ Trees, so let's continue with this assumption.

You've also implicitly indicated that there's always only one resulting row in a SELECT, which is hardly a representative case; plenty times do we look for 1,000 rows at a time. But let's continue with the assumption of a single matching row.

Your next assumption is that searches are always done by the indexed column. This is fine, but I'm just noting that DELETEing a record by some unindexed column Y turns out to be more expensive: you're both wasting O(n) time in finding the record, and then paying an additional O(log(n)) for updating the index.

But let us continue with the assumption that we're only discussing queries which are looking up at indexed columns.

Some tables use unclustered index format (which fits into your calculations) - the table is one entity and the index is another. Others use clustered index format: table rows (or rather blocks of rows, or rather yet pages of rows) are actually stored as leaves inside the clustering index. In such scenario, you will pay O(log(n)) for finding a record in an INSERT command. An optimization for that is in the case you're inserting to the end of the table, and a decent implementation would hold a pointer to the last record/page in the index. (Oh, yes, you should not the possibility that your record gets INSERTed to the middle of the table).

Actually, records could get inserted to the middle of the table even in unclustered tables; it would make sense to spend more search time so as to avoid fragmentation, and at least one implementation that I know of does that. I'm assuming other may, too.

Deleting/Inserting an index from a BTree is O(1) on average, but may cost up to log(n) operations in case of propagated page merging/splitting.

Also, the fact that always comes as a surprise to many, is that sometimes a table scan is faster than index lookup. This is particularly true for queries resulting in multiple rows. It turns out looking up the index adds overhead; when compared to full table scan the overhead could actually make total cost higher. For single row lookup the vast majority of index lookups should be faster than a full table scan.

But do consider the following general convention: you pay with dollars any action that accesses disk. You pay with nickels actions that act on in memory data. This is actually at the heart of database disk I/O optimization. If index pages are on disk, and table pages happen to be in memory, you will possibly pay less for table scan.

And that's where havoc comes in: it really all depends on your workloads, on your memory size, on your dataset size.

Did you ever take a math class where you had to solve some complex integral? It took you hours to solve it, and got point off for missing some tiny minus sign somewhere?

Did you even take a physics class where you had to solve some complex integral? The professor would just throw away chunks of the equation, saying "this is neglect-able", and you would rip your hair off? WHY is it neglect-able? Why not other things?

Computer science is based on math. Computers are based on physics. They are physical objects. They need to spin disks, access a memory bus, manage billions of transistors... You just can't actually anticipate what will happen and put it all under some equation.

It may just turn out that for some particular dataset your entire equation does not hold water. In other times, it may be just fine.


In Postgres, how can I calculate cume_dist (relative rank) over the entire table, but only for a single row?

You need to first apply the function in the whole table and then restrict the results with WHERE:

SELECT r.rank
FROM
  ( SELECT f.data, cume_dist() OVER (ORDER BY f.count ASC) AS rank
    FROM foo AS f
  ) AS r
WHERE r.data = 'bar';

Alternatively, you could calculate the function with 2 subqueries:

SELECT
    ( SELECT COUNT(*)
      FROM foo
      WHERE count <= (SELECT count FROM foo WHERE data = 'bar')
    )
  * 1.0 /
    ( SELECT COUNT(*) FROM foo )
  AS rank ;

or with the new FILTER syntax:

SELECT COUNT(*) FILTER
                (WHERE f.count <= (SELECT count FROM foo WHERE data = 'bar'))
       * 1.0 / COUNT(*) AS rank
FROM foo AS f ;


Read Committed Snapshot Isolation blocking during table modification

Are you doing online rebuilds of this index at the same time as the transaction?
I have RCSI block if there is an index rebuild simultaneous with a large or long running transaction.
Paul Randal has a great explanation here: http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-830-unicorns-rainbows-and-online-index-operations/


Will I be notified that BACKUP encountered a CHECKSUM error if I use CONTINUE_AFTER_ERROR?

Yes it will continue and finish, there is an example in this older blogpost. It also contains a broken database file (for older versions) to play with:

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2007/04/17/example-corrupt-database-to-play-with-and-some-backuprestore-things-to-try/

I think it is actually quite important as a dba to simulate all those cases, I know it from experience with customers it is risky and time consuming if you have to research those methods in a downtime. So kudos for asking. Here is a lunatic/ingenious method to do corruption with SQL:

https://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/03/10/how-to-create-a-corrupt-database-using-bulk-insert-update-and-bcp-sql-server-as-a-hex-editor.aspx


Modifying initial values for an AUTO_INCREMENT column

MySQL docs for CREATE TABLE explain almost everything about AUTO_INCREMENT columns:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

-- irrelevant stuff omitted

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]

-- irrelevant stuff omitted

table_option:
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value

-- many stuff omitted

There are two AUTO_INCREMENT settings, the column attribute and a table option that can change the initial value of the auto incremented column from the default (1):

  Column Data Types and Attributes

  AUTO_INCREMENT

  An integer or floating-point column can have the additional
  attribute AUTO_INCREMENT. When you insert a value of NULL
  (recommended) or 0 into an indexed AUTO_INCREMENT column, the column
  is set to the next sequence value. Typically this is value+1,
  where value is the largest value for the column currently in the
  table. AUTO_INCREMENT sequences begin with 1.      ...

  Table Options

  AUTO_INCREMENT

  The initial AUTO_INCREMENT value for the table. ...

So, to add a modify a column with the AUTO_INCREMENT attribute that starts from a 100, you do:

ALTER TABLE infrastructure.mfg
    CHANGE COLUMN mfg_id mfg_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    AUTO_INCREMENT = 100 ;

Test in dbfiddle.uk

Further information can be found in the related pages of the documentation:

Using AUTO_INCREMENT
AUTO_INCREMENT Handling in InnoDB

Particularly in the last one above, it explains what options you have regarding changing the increment from 1 to 100. This isn't trivial:

  InnoDB increments the value retrieved by the statement and assigns it to the column and to the auto-increment counter for the table. By default, the value is incremented by 1. This default can be overridden by the auto_increment_increment configuration setting.

  If the table is empty, InnoDB uses the value 1. This default can be overridden by the auto_increment_offset configuration setting.

Test 2 in dbfiddle.uk

Note that these two variables affect all the tables in the MySQL Server.


Use stored procedure to get minimum value from column and decrement it and other values

This example code should work, but there are some things to watch out for.
Since this is updating the tables while (presumably) another process is using that table to schedule jobs, concurrency could be an issue.

For example, I'd assume there is another key field not mentioned that the scheduler process is using to identify the jobs. If it's the xNumber/xMachine combination, then changing them could cause an issue.

    CREATE TABLE #xTable (xNumber INT , xMachine INT)

    INSERT #xTable ( xNumber, xMachine ) VALUES (5,1),(6,1),(7,1),(8,1),(9,1),(7,2),(8,2),(9,2)
    SELECT * FROM #xTable AS xt;

;
WITH    cte ( xMin, xMachine )
        AS ( SELECT   MIN(xNumber)
            ,       xMachine
            FROM        #xTable
            GROUP BY xMachine
           )
    UPDATE     #xTable
    SET        xNumber -= cte.xMin - 1
    FROM       #xTable
    INNER JOIN cte ON cte.xMachine = #xTable.xMachine;

    SELECT * FROM #xTable AS xt

    DROP TABLE #xTable;

Initial values:

xNumber xMachine
5   1
6   1
7   1
8   1
9   1
7   2
8   2
9   2

Modified Values:

xNumber xMachine
1   1
2   1
3   1
4   1
5   1
1   2
2   2
3   2


How to handle Schedules when moving Jobs to another server?

From my experience scripting out jobs and running on another servers works fine - including schedulers.
You might want to check job owner login to appear on destination server.


Possible options for the ON DELETE and ON UPDATE clauses are explained in the CREATE TABLE page of the manual.

The default is NO ACTION, so if you have any foreign keys defined without explicitly specifying what action to be performed on updates of the referenced primary or unique key, it is the same as if ON UPDATE NO ACTION was specified:

  NO ACTION

  Produce an error indicating that the deletion or update would create a foreign key constraint violation. If the constraint is deferred, this error will be produced at constraint check time if there still exist any referencing rows. This is the default action.

If you have specified ON UPDATE CASCADE on a foreign key, the referencing rows will be updated accordingly when a referenced row is updated:

  CASCADE

  Delete any rows referencing the deleted row, or update the value of the referencing column to the new value of the referenced column, respectively.

Primary keys are usually not updated (and for good reasons) but there are situations where it can be useful. Plus, the foreign key may be referencing not a primary key but a (rarely updated) unique key.


We have

X (A, B, D, E, F, G, H, J) with A and B as primary key
Y (K, L, M, N, O, P, Q, R) with K and L as primary key

With functional dependencies:

A -> G, H
B -> D, E, F
E -> F
H -> G
L -> N, O, P, Q
N -> Q

2NF Normalization

R1((A,B) (PK), J)
R2(A (PK) ,G,H)
R3(B (PK) ,D,E,F)
R4((K,L) (PK), M,R)
R5(L (PK) ,N,O,P,Q)

3NF Normalization

Ra((A,B) (PK), J)
Rb(A (PK) ,H)
Rc(H(PK), G)
Rd(B(PK),D,E)
Re(E(PK),F)
Rf((K,L) (PK), M,R)
Rg(L (PK),N,O,P)
Rh(N (PK), Q)


AWS DMS: 2 million CDCIncomingChanges resolved without target throughput

It would seem that every row in a DB was effectively being deleted and recreated without any changes, and that batchApply was enabled.  Batch apply would effectively cancel out large numbers of changes, which is why the number of CDCIncomingChanges was reduced without throughput.


2 Very similar queries return different number of result (one is null)

You write sea* here and Sea* there.  This bug report (which turned out not to report a bug, but a (for me at least) cryptic feature) can be useful for you. (I found the link by searching for case sensitivity in full text search.)


Regardless of how you solve the problem, you have to go through some sort of "router".  And that may be the least secure part.

Separate computer for each customer -- you need to tell user which machine to go to.

Separate process or API or VM on same machine -- user must log into the appropriate path.

Separate databases (one per customer) on single MySQL server -- either API or MySQL could handle security.

Single database, but separate rows in the tables -- Now the API must take responsibility for security.  That is, the user must not have direct access to MySQL, only indirect through an API layer.

Each has "privacy by design".


Find timestamp for highest number of concurrent users by date

In PostgreSQL you can simply use DISTINCT ON:
SELECT DISTINCT ON (concurrent_date) *
FROM   sample_data
ORDER  BY concurrent_date, concurrent_users DESC;

We don't need the redundant column concurrent_date for this at all. Casting concurrent_timestamp on the fly is very cheap - overall cheaper than storing the functionally dependent value redundantly.
SELECT DISTINCT ON (concurrent_timestamp::date) *
FROM   sample_data
ORDER  BY concurrent_timestamp::date, concurrent_users DESC;

db<>fiddle here
See:

Select first row in each GROUP BY group?

DISTINCT ON is typically fastest for few rows per group. Depending on table definition and data distribution, there may be (much) faster solutions. See:

Optimize GROUP BY query to retrieve latest row per user


Can we migrate oracle database 10gR2 to cross platform

To answer your question if you can migrate a linux database to a higher oracle release on a windows platform: Yes you can.

There are a few things to keep in mind...

Linux and windows use little endian, unix uses big endian -- basically it depends on the CPU not so much on the OS.

If you want to minimize downtime and restore the database on the windows server while the linux server is still productive, you have to apply the archivelogs from the linux database on the windows database until you do the switch. Basically this should work but there are some bugs I faced when doing this. So I would not recommend this option at least not with 10g or 11g (never tried it with 12c). Oracle officially supports DataGuard with windows and linux from 11gR2 but as I said there are some bugs. It failed in 3 of 5 attempts.

I personally don't like export-import migrations because you always have some trouble with invalid objects, characterset conversion, missing data, missing public synonyms etc.

--> I would recommend the the following action plan:

shutdown the linux database
startup mount the linux database
create an rman backup
restore the database on windows
open the database with alter database open resetlogs upgrade
run catupgrd.sql
do post-upgrade tasks


Find if any of the rows partially match a string

You can use LOCATE() – as in the other answer – or LIKE:

SELECT *
FROM site_core_antispam_banned_domain
WHERE 'subdomain.com' LIKE CONCAT('%', domain_name, '%') ;

The above will look for domains that match any part of a given string. If you specifically wanted domains matching the right side of the string (for instance, if the domain to match against was somedomain.com.ro and you were only interested in *.com.ro results), you could make the pattern more specific:

SELECT *
FROM site_core_antispam_banned_domain
WHERE 'subdomain.com' LIKE CONCAT('%', domain_name) ;


INT(5) vs SMALLINT(5): numbers in parenthesis after the numeric type

An int and a smallint have different sizes and consequently ranges.
The (5) is smallint(5) or int(5) is called a "Numeric Type Attribute" and it represents the "display width" of the field,

  MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

  When used in conjunction with the optional (nonstandard) attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(4) ZEROFILL, a value of 5 is retrieved as 0005.

This is a MySQL "extension", and zerofill is an "extension" on an extension.

For the sane method of controlling the display of a numeric type, don't ues Numeric type Attributes and instead use

FORMAT
LPAD
RPAD


Unable to perform queries through mongos

Well, finally I've solved the problem. It was my fault when configuring everything to give the same name data to the replicas of the config server and the sharded server.

I replaced data by rs0 when launching the config servers:

mongod --configsvr --replSet rs0 --bind_ip localhost,${HOSTNAME}\
    --port 25000 --logpath /var/lib/mongodb/shardedcluster/log.cfg\
    --logappend --dbpath /var/lib/mongodb/shardedcluster/cfg --fork

Of course in the first rs.initiate changed _id to rs0 and finally when launching the mongos replaced data with rs0:

mongos --configdb data/mongoserver1:25000,mongoserver2:25000,mongoserver3:25000 \
    --bind_ip localhost,${HOSTNAME} \
    --logpath /var/lib/mongodb/shardedcluster/mongos.log --fork

Now it works like charm :)


Transactional Replication is the best fit for this scenario.
Your tables should have primary keys, which is a pre-requisite for Transactional Replication.  The load on the publisher is minimal.  The bulk of the work is done by the Distributor, which can be on the same instance as the Publisher, or the Subscriber, or a completely separate SQL instance.
If you are willing to upgrade the production database to SQL Server 2017 Enterprise Edition you can create a readable replica on the secondary with AlwaysOn Availibility Group, with or without joining the servers in a Windows Server Failover Cluster.


DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += [sql] FROM
(
  SELECT [sql] = N'
  ALTER TABLE '
    + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
    + ' ALTER COLUMN ' + c.name + ' VARCHAR(50);'
  FROM sys.columns AS c
  INNER JOIN sys.tables AS t
  ON c.[object_id] = t.[object_id]
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE LOWER(c.name) = N'userid'
  AND c.max_length < 50
  UNION ALL
  SELECT N'
  ALTER TABLE '
    + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
    + ' ADD UserID VARCHAR(50);'
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE NOT EXISTS
  (
   SELECT 1 FROM sys.columns
     WHERE LOWER(name) = N'userid'
     AND [object_id] = t.[object_id]
  )
) AS x;

PRINT @sql;

--EXEC sp_executesql @sql;

If the string is too long to properly validate via PRINT, see this tip for other ideas.

You'll want to also make sure that you update any stored procedures that take this as a varchar(9) parameter, since these will silently truncate, and also any explicit conversions or variable declarations you are doing in ad hoc SQL, views and other modules, etc. For example if you have two users, one with ID = frankenstein and the other just frankenst:

DECLARE @UserID VARCHAR(9) = 'frankenstein';
SELECT @UserID;

Result:

frankenst -- this will match the wrong user!


Is it possible to build Data warehouse by myself?

It's a lot like asking, "Can I build a house by myself?"

It depends a lot on what your definition of "house" is. Talk with your internal users to build a set of requirements first - that's going to need to happen regardless of who builds it.

When you're done building requirements, then you can start sketching out what you'll need to build in order to satisfy those requirements.

Then, you can start building time estimates for what needs to be built.

As you work through this process, you'll have a better understanding of what the house looks like and whether you can build it yourself with your existing toolbox and skills.


Query data that does not fulfill constraints in table in Oracle DB

Reporting Constraint Exceptions

  You must create an appropriate exceptions report table to accept
  information from the EXCEPTIONS option of the ENABLE clause before
  enabling the constraint. You can create an exception table by
  executing the UTLEXCPT.SQL script or the UTLEXPT1.SQL script.

  The following statement attempts to validate the PRIMARY KEY of the
  dept table, and if exceptions exist, information is inserted into a
  table named EXCEPTIONS:

ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;

  If duplicate primary key values exist in the dept table and the name
  of the PRIMARY KEY constraint on dept is sys_c00610, then the
  following query will display those exceptions:

SELECT * FROM EXCEPTIONS;

  The following exceptions are shown:

fROWID               OWNER      TABLE_NAME      CONSTRAINT
------------------  ---------  --------------  -----------
AAAAZ9AABAAABvqAAB  SCOTT      DEPT            SYS_C00610
AAAAZ9AABAAABvqAAG  SCOTT      DEPT            SYS_C00610


What indexes would I create for a query like this?

I would suggest the following two indexes:

charges(balance_id, refunded, invoice)
balance_transactions(account_id, type, created, source, fee)

The first is a covering index for charges.  The index should be used for the query with no need for the data tables.

The second is a covering index for balance_transactions.  The first three will satisfy the where clause.  The four column will be used for the join and the final is needed for the select.


"Index merge intersect" is rarely picked because it involves getting lots of row pointers from two indexes, then intersecting the sets, and only then looking back in the table.

It is always better (as far as I know) to build a composite index:

INDEX(id, flag)  -- in either order

If this is a 1-row table, then the effort of looking in two indexes to ultimately get a single row -- this is a waste.

A side note:  An InnoDB table should always have a PRIMARY KEY.

Put a thousand realistic rows in the table and add a PRIMARY KEY.  Then we can discuss this further.  (But the answer will be the same -- use a composite index instead of hoping for "index merge intersect".)


How much free space do I need for mongoDB? and how to reclaim lost space?

There are two operations here
1. Compact - This is an in place compact of data inside the data files. This will however not release any of the data files. Even thought it does not return the free space to the file system mongo should be able to use the space for growth.
2. Repair - This will release any unused space back to the filesystem. This is what you need. However repair requires the same amount of space as your database. So if you want to repair you will 800GB + few extra GB for repair to complete.


Update all tables containing column name where the field is something specific

This answer will require some dynamic SQL and potentially could make use of a while/do loop.
Basically the idea will be to figure our how to define your list of tables and column names.  If you really are just looking for a list of tables where a column name exists, you will probably want to make use of the sys.syscolumns table to query the column names.

SELECT o.name
FROM sys.syscolumns c
    JOIN sys.sysobjects o ON c.id=o.id
WHERE UPPER(c.name) = 'Description'

From there you can build your dynamic sql statement.

DECLARE @sqltext VARCHAR(MAX)
DECLARE @UpdateValue VARCHAR(30) = 'SetThisValue'

SELECT @sqltext = ISNULL(@sqltext,'') + 'UPDATE '+o.name + CHAR(10) + ' SET     [YourColumnNameHere] = ' + @UpdateValue + CHAR(10)
    + ' WHERE [YourColumnNameHere] = [Somevalue]'
FROM sys.syscolumns c
    JOIN sys.sysobjects o ON c.id=o.id
WHERE UPPER(c.name) = 'Description'

PRINT @sqltext
EXEC sp_executesql @sqltext

I have the print statement in there to help you get the @sqltext exactly the way you want it.  Once you have it as you want, use the EXEC sp_executesql line to actually run the output of the @sqltext.


Memory utilization for multiple instance (SQL server 2008R2 standard edition)

You can leave it to default for all instances because since it is SQL Server 2008 R2 standard edition it will not use more than 64 GB and you ahve total of 256 GB RAM so you are on safer side. Please also note that 64GB limitation is for Database Engine only other components like SSIS,SSAS,SSRS does not falls under this restriction and yes they can also consume good amount of memory.


Having different Table space for different indexes of same table, will causes any performance impact?

Placing the indexes for a table in the same or different tablespaces from the tables and other indexes on the same table should have no impact on performance unless you have done something very wrong with your disk farm.


So for some tables it looks like

Well, it should be that way for all the tables.

That message is generated by the --verbose option, and you should see it on all the tables, when mysqldump makes its second pass through the database.

Because views can't reference nonexistent objects, mysqldump iterates through all of the tables in each database, twice, if views are encountered.  On the first pass, it creates a dummy table for views so that, if it turns out a view references another view, the dummy table stands in place of the target view so that the referencing view can be valid while the restoration of the backup takes place.  If views were encountered, it makes a second pass, dropping each dummy table and replacing it with the actual view.

It does this by calling mysql_list_tables() in the c-client API, which sends "show tables" to the server (after switching the current database).  Since this contains a list of base tables and views, mysqldump has to iterate through each one and decide what the current identifier represents -- a table, in which case, it does nothing because the table was already dumped -- or a view, in which case it drops the dummy table and creates a proper view.  (Why it doesn't discover this information more intelligently is probably related to backwards compatibility with ancient versions of MySQL Server).

In short, this is normal.  The --verbose messages are just telling you something you already knew.  When you see these, mysqldump is iterating through the list of all tables in the database, checking whether they are views or not (for the second time).

The base tables are dumped and dummy view "placeholder" tables are created during the first pass; the base tables are skipped, placeholder tables dropped, and view definitions are dumped during the second pass.

The message -- Retrieving view structure for table users... is poorly worded, since in fact mysqldump is, at that point, checking whether the table is a view, by requesting its CREATE statement from the server, and the message appears to have given the impression in the original question that the table was first mischaracterized as a view, then omitted from the dump because it was not a view. This was an inaccurate perception, as the table had already been dumped during the first pass.


Error after upgrading datastax to version 5

Insufficient permissions on directory /hints

I'm guessing that your "cassandra" user doesn't have permissions to add a new directory on its own.  Similar to the tarball installation process, you'll need to create the directory yourself.

  where the location for this directory?

By default, the location for /hints is:

$CASSANDRA_HOME/data/hints

You can also override this in your cassandra.yaml, with the hints_directory property:

hints_directory: /var/lib/cassandra/hints


Mapped drive for Microsoft SQL Server 2008

I've got explanation ( tricky ). Maybe someone could get use of it:

Administrator might set maximum number of connections from one account to external resources ( in this example - drive ). When the number of connections is reached, You don't have access to it ( and from SSMS it is not visible ).

Recommendation:

Check, using for example sp_who, do You have any sleeping processes on Database Engine Service Account. That requests could hang on external resources, and block access for other requests.


There are 2 types of SQL Server installed on a PC:

Server based - named instances such as \sqlexpress, \instance name or unnamed instances such as . All of these instances can be stopped by going to services.msc and stopping the service. By default, most people install unnamed instances on PCs unless they fully intend to install a named instance.
File-based - this is your localDB that may come with an installation of Visual Studio

From all indications, you may have more than one server-based installation - a unnamed instance for which you have stopped the service in services.msc and a named instance which you can still connect to.
I would go to services.msc and check to see if any of the sql instances services have a status of running. If they do, that’s why. While in services, typing “s” or “sq” fast enough should get you there faster.
Sql configuration is great for particular versions but may not reveal other instances beyond the version for which it was installed.


Update a table faster than an INNER JOIN

I have added this index and it has sped up the join to 0ms average duration:

CREATE INDEX [IDX_FLAG_LEVEL_CODE]
     ON Code (CommissioningFlag, AggregationLevelId)
     INCLUDE (Serial, CodeId);

I found the solution by chance, while resolving my previous question.


An example of a relation without any functional dependency?

But am I right, that every relation has at least one functional dependency?

Relation can has no functional dependencies, if it has no non-key attributes, i.e. it is full-keyed relation.


Is there any benefit in limiting the number precision on a surrogate PK in Oracle?

Just use

my_key number not null

If you are generating the primary key using an Oracle sequence (PS: you should), you are guaranteed to get integers so you don't have to have Oracle checking precision, etc. if you specified number(7,0) to specify an integer with up to seven digits.

And then if you ever exceed 7 digits, not a fun day to fix that.  As the links you've referenced above mention that Oracle only uses enough space to store the number, no need to worry about how much storage is being used; and therefore you don't have to concern yourself with indexing of the columns holding the primary and foreign keys.

The only time I would specify a fixed length integer would be if the primary key was an intelligent key such as a USA social security number, but intelligent keys are a bad idea too.


Unfamiliar syntax - Query with Parameters in Braces at the beggining

You are right, the (@be int) shown applies to parameterized queries. Applications often parameterize the queries using sp_executesql, and then sent them to the sql server.

The query will be cached as (variables)QueryText
. And ofcourse, the values won't be cached in the text, since the query is parameterized.

Parameterized query example

Test Data

CREATE SCHEMA PERSON;

CREATE TABLE Person.Person( BusinessEntityID int );

INSERT INTO Person.Person(BusinessEntityID)
VALUES(1),(2),(3);

Query

exec sp_executesql N'SELECT  * FROM    Person.Person WHERE   BusinessEntityID = @be',N'@be int',@be=2

See the result in the cache with this query

select text from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%Person%';

or

(@be int)SELECT  * FROM    Person.Person WHERE   BusinessEntityID = @be

With a procedure the caching will be different

Create the procedure

use test
go
create procedure dbo.myproc @dbname varchar(255)
as
select * from sys.databases where name = @dbname

Run the proc

exec dbo.myproc @dbname= 'master';

Result in cache

select text from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%myproc%';

or

create procedure dbo.myproc @dbname varchar(255)  as  select * from sys.databases where name = @dbname

This can also happen when forced parameterization is enabled

Enable forced parameterization

ALTER DATABASE test SET PARAMETERIZATION FORCED

Same query, without parameters

SELECT  *
FROM    Person.Person
WHERE   BusinessEntityID = 5

Result in cache

select text from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%Person%'

or

(@0 int)select * from Person . Person where BusinessEntityID = @0


How do I handle importing high amounts of transaction data into application DB?

We have a good sized Enterprise system that has many of SSIS packages taking data in and out of the system daily.

Some of the strategies we have used are:

Only process deltas especially in large files. To figure which records are deltas we use change tracking and send that data to tables in a separate database, so the process of figuring out the deltas when we receive a full file is pushed off onto a staging server. Only records that are new or changed go to the real production server.  On our busiest server, we have moved all teh processing except the final load off to a completely separate server.

If the file goes directly to table that does not also have transactional data changes or multiple data sources, we might have two tables (and A and B version) and a view that selects from the active table. So we do our processing on the inactive version (possibly including dropping and recreating indexes), switch the table in the view (meaning there is about 1 second of down time fromteh user perspective), then update the new inactive table.

SSIS packages can be designed to run very quickly or not so quickly. So we may spend a lot of time performance tuning one that takes too long and affects production.

Run the packages during the time period when there is the least usage on the server.


PGAdmin III Server Status Error: Could not read directory "pg_log" No such file or directory

Your client (Windows) is running on a different machine than your server (Ubuntu).

You cannot read the remote file system. Inspecting the logs only works if your client is on the same machine (or has access to the remote file system by way of some advanced trickery.)

To suppress the error message remove the log file from the display.
In the Server Status window press
Ctrl-Alt-F
Or uncheck View -> Logfile.


Does anybody know what this tool is called?

It's a pair of canvas pliers. They're used to stretch canvas over braces, used in painting prep.

Answer source: Matt Velic, data professional and noted art graduate.


Following mysql query is taking too much time for excecution

table2 needs INDEX(tableid, user_id) (in either order).

If this does not solve the problem, please provide SHOW CREATE TABLE for both tables, plus EXPLAIN SELECT ....


The security concern with SMTP from a database server is the potential for disclosure of sensitive information.  For example, a user that has (or gains) permissions to execute sp_send_dbmail could send an email with sensitive information.  Disallowing outbound access to the default SMTP port is a security measure security folks sometimes insist upon to limit such exposure.  It is common to lock down overall database server network traffic in an environment with particularly sensitive information such that network connectivity is allowed only to ports/hosts specifically needed, and permitted only after approval by the security department.

You need to work with your security team if your organization does not yet have an infrastructure for database administrative alerts.  They may allow a security exception for SMTP if there are additional measures in place to prevent ad-hoc email access on both the database and SMTP server.


Postgres group and aggregate(sum) JSONb arrays and non JSONb properties

Ok, so i've got a working query that is happening in an acceptable timeframe.  It feels ugly, so if there are obvious ways I can improve it please do let me know.
with base_data as (
        /*This is where the query for incidents/static assets goes*/
        select affected_sectors, involved_parties, reported_at, tags, casualties
        from incidents
        ------------------------------------------------------------
)
select  /*unique affected_sectors*/
        (
            select jsonb_agg(ssect.sector)
            from (
                select sect.sector
                from base_data,
                 jsonb_to_recordset(base_data.affected_sectors) as sect(id varchar, sector jsonb)
                group by sect.sector
                ) ssect
        ) unique_sectors,
        /*unique involved parties*/
        (
            select jsonb_agg(spart.group)
            from    (
                select grp."group"
                from base_data,
                jsonb_to_recordset(base_data.involved_parties) as grp(id varchar, "type" varchar, "group" jsonb, involvement varchar)
                group by grp."group"
            ) spart
        ) unique_groups,
        /*min reported at date*/
        (
            select min(reported_at) from base_data
        ) min_reported_at,
        /*max reported at date*/
        (
            select max(reported_at) from base_data
        ) max_reported_at,
        /*unique tags*/
        (
            select jsonb_agg(stags.tags)
            from    (
                select value tags
                from base_data,
                jsonb_array_elements(base_data.tags)
                group by value
            ) stags
        ) unique_tags,
        /*summary casualty counts*/
        (
            select json_object_agg(key, val)
            from (
                select key, sum(value::numeric) val
                from base_data cas, jsonb_each_text(cas.casualties)
                group by key
                ) scas
        ) casualty_counts,
        /*Incident Count*/
        (
            select count(1) from base_data
        ) incident_count

In our database this runs in around 700ms for 10000 incidents on a clear cache.
I'd have liked this to be sub 200ms and will continue to hack at it.  If I come up with anything more useful I'll add a comment.


Ok, based on your very limited documentation, I would do the following:

Fact Tables - Your fact table is your measurement table. It is the thing that happened. It is the meeting together of dimension tables, typically at a point in time.  In your diagram,

RESPONSES is your fact table.
OPTIONS could be a fact table.  If you denormalize it you could have dimensions associated with it.

Dimension Tables - Dimension tables contain the textual context associated with a measurement event. It describes the “who, what, where, when, how, and why.” Based on your diagram:

SAMPLE would be a dimension table, describing the people who make a response. It would be the closest you have to a "customer" dimension table.
QUESTIONS would be a dimension table, describing the question in detail. You could extend this dimension to include more data on the question type, such as length, IsMultipleChoice, IsFreeText, IsPersonal, isPrivate, etc. I would think of this dimension as a "product" dimension table, based on the limited schema you have.
OPTIONS could be a dimension table. It looks like it is something you base the response field on. The possibility of responses for a given question.

Personally, I think you need to read up on Data Warehousing. The Data Warehouse Toolkit by Kimball is an invaluable resource.


Why query cache plan kept dynamic queries but not sp?

Cached Plans are typically only removed from the plan cache under memory pressure.

SQL Server primarily considers the cost of the plan when deciding which plans to remove.  Lost-cost plans are removed before high-cost plans.  The "cost" here is not directly the same as the "plan cost" you see when looking at execution plans - it's a costing mechanism associated with the cache.

When SQL Server detects memory pressure, it removes zero-cost plans from the cache, then reduces the cost of the remaining plans by 50%.  For ad-hoc plans, the cost of the plan is considered to be zero, however that cost is increased by one every time the plan is reused.  If you have "optimize for ad-hoc plans" enabled, it's likely the dynamic queries you're seeing in the cache have been heavily used, and as a result have a "high" cache cost, and are not being evicted.  For non-ad-hoc plans, the plan cost is not incremented each time a plan is used, but is kept at the original plan cost, which is based on the execution-plan cost.  You can see the number of times a particular plan has been used in the sys.dm_exec_cached_plans DMV.

Cache cost is measured in units known as "ticks", with a maximum of 31.  Ticks are incremented according to this:

each I/O adds 1 tick, up to a maximum of 19 ticks.
each context switch adds one tick, up to a maximum of 8 ticks.
one tick per 16 pages of memory, with a maximum of 4 ticks.

Ticks start to be decremented once the plan cache reaches 50% of its capacity.  At that point, SQL Server initiates a resource monitor thread that decrements each plan tick-count by 1 each time the cache is populated with another plan.

The sys.dm_os_memory_cache_entries DMV contains details about what objects are in the cache, along with the original and current costs, among a number of other useful metrics.

The details listed above are based on details found in SQL Server Internals by Kalen Delaney.


Common `event` (datetime) table for say a manufacturing DB, an anti pattern?

Having an EVENT table sounds reasonable, particularly if there is a set of attributes common to all events. I would tend towards a more descriptive table name, perhaps MANUFACTURING_EVENT (or some terminology used by people in that industry). I would then have detail tables for specific manufacturing events with a FK relationship to the parent.


Suppress display in a column if the value is equal to the value in another column


Any tips to watch out for when setting one of my databases to Read Only?

is it simply just changing the Database Read Only State to "True"?

yes, you can use below t-sql

alter database db_name
set read_only with rollback immediate

  Also, is it a good idea to change the recovery model for this old production database to "Simple" vs "Full"?

Yes, a read-only database wont be having any DML operations. So best practice is to change the recovery model to SIMPLE. Make sure that you take regular FULL backups (and most important is testing your restore) - just incase if the server goes down.

  In worse cases, I always can set it back to Read and Write at anytime with no issues correct?

Yes, you can just run below tsql

ALTER DATABASE db_name SET READ_WRITE
with rollback immediate
GO


Deleting data from a large table with replication

Instead of doing TRUNCATE TABLE (which locks up any connections accessing the table), try making an empty copy of the table, swapping it in, and dropping the old table.

EXAMPLE

Suppose the table is called mydb.mytable. Do it like this

USE mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;

Doing it this way let's mytable become empty immediately and does not get locked during the deletion of the data. Now, this should go quick on the Master and should replicate. The last line DROP TABLE mytable_old; might take the longest. If that concerns you, then run this on both Master and Slave (based on the answer from Jynus)

SET sql_log_bin = 0;
USE mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;
SET sql_log_bin = 1;

GIVE IT A TRY !!!

CAVEATS

As for deleting data in small chunks, that would not be a good idea if you need to table immediately available after the table data is deleted. Why ?

Doing DELETE FROM mytable.mytable; is a single transaction. All the rows would be prepared for rollback in the InnoDB Architecture. After the DELETE is complete, all that MVCC info has to be discarded. That explains why it takes so long.
Deleting in small chunks would just create more transactions and smaller rollbacks. Notwithstanding, it still creates the same amount of rollback information and work. It may allow you to monitor how much data is left to delete.
Please don't run DELETE FROM mydb.mytable LIMIT 1000;. Using LIMIT on a DELETE without a WHERE clause is not replication safe.


How do I find out the "redo log file max size" in Oracle 11R2

If you want the size of the redo log members (files on disk) size, then use the query below:

-- Show Redo Logs info
set linesize 300
column REDOLOG_FILE_NAME format a50
SELECT
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    b.MEMBER    AS REDOLOG_FILE_NAME,
    (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ASC;

The query output should look like:

GROUP   #THREAD#  SEQUENCE# ARC STATUS   REDOLOG_FILE_NAME      SIZE_MB
---------- ---------- ---------- --- ---------------- ---------------------
1       1       4 YES INACTIVE /ORACLE/oradata/orcl1/redo01.log   50
2       1       5 YES INACTIVE /ORACLE/oradata/orcl1/redo02.log   50
3       1       6 NO  CURRENT  /ORACLE/oradata/orcl1/redo03.log   50


Upgrading Operating System only. OS 2016 - 2019

I suggest you read up on Database Compatibility at https://aka.ms/dbcompat
What this will tell you is that validation is not done at the SQL Server major version level but at the database level.  This should ease your concerns about upgrading from SQL Server 2017 to 2019.
However, if you are staying on the same version for cost purposes (e.g. you don't have software assurance to get free upgrades) then running SQL Server 2017 on Windows 2019 is fully supported and does not change the features or functionality of SQL Server.  See the support matrix for confirmation of that


Reference:

Backing up a VLDB to Azure Blob Storage by Dimitri Furman
Understanding Block Blobs, Append Blobs, and Page Blobs
SQL Server Backup to URL

With your size you should be able to use page blog and specify MAXTRANSFERSIZE = 4194304 and BLOCKSIZE = 65536.
If you need to backup databases greater than 1TB use block blob and striped backup. To use block blobs, you need to create a Shared Access Signature (SAS), and create a credential for the SAS.  There are examples in the third reference above.

There are bunch of questions answered in the comment section of the first reference that will help in case if you run into any issues.


Both queries have the same execution plan. You can check that in SQL Server Management Studio by typing:
WITH CTE1 AS ( SELECT Col1, Col2, Col3 FROM dbo.Table1
WHERE Col3 = '1' )
SELECT * from CTE1

;WITH CTE2 AS ( SELECT Col1, Col2, Col3 FROM dbo.Table1
)
SELECT * from CTE2 WHERE Col3 = '1'

Regarding the second part of your question: it's quite hard to say without looking into actual code and understanding what you are trying to achieve, however you may find that using temporary table may be way more efficient than CTE.


SQL Server 2012 linked server to SQL Server 2000

Using SQLNCLI10 to create a linked server to SQL Server 2000 causes a fault. You can create an odbc connection than attach tables to your 2012 database. Then you build your treatments in SQL 2012 database as if the table was native. This is just a workaround that sometimes works. It's not recommended for large data transaction, like more that 7MM.


Can the Reorganize Index task (maint' plan or ssis package) be used to 'performance tune?

Performance tuning is an art. You should avoid knee jerk performance tuning methods - instead you should try to narrow down your investigation.

  Is this an adequate step of triage for degraded application/query performance?

It may or may not be, depending on how many pages the index has and how bad the fragmentation. Brent Ozar has a nice post on - Stop Worrying About SQL Server Fragmentation and Kendra Little explains - Why Index Fragmentation and Bad Statistics Aren’t Always the Problem (Video) ?

What I would suggest you is have a baseline - e.g you can baseline your waitstats and check for any anomalies as a starting point. Then you can narrow down your investigation and find the root cause and address it.

Depending on sql server version you are using, use SQL Server Diagnostic Information Queries or SQL Server Health Check Script - sp_Blitz. These are free and very efficient tools written by experts and given to the community as free tools.

As a side note, as RLF suggested - use SQL Server Index and Statistics Maintenance for doing index maintenance. Its very widely used in the community and its free as well. Dont reinvent the wheel by writing your own solution.


It is not possible to directly connect part of the query text (e.g. GROUP BY) with a specific operation in the final execution plan.

You can write a query to find plans that:

Contain a Hash Match Aggregate; and
The query text contains a GROUP BY clause

...which is not quite the same thing, since this will find plans where the grouping logic was implemented using a Stream Aggregate, replaced with another operation, or even removed entirely - but happens to also contain a Hash Match Aggregate for some other reason.

For example:

WITH XMLNAMESPACES
(
    DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
SELECT
    DECP.cacheobjtype,
    DECP.objtype,
    DECP.plan_handle,
    DEQP.objectid,
    DEQP.query_plan,
    DEST.[text]
FROM sys.dm_exec_cached_plans AS DECP
CROSS APPLY sys.dm_exec_query_plan(DECP.plan_handle) AS DEQP
CROSS APPLY sys.dm_exec_sql_text(DECP.plan_handle) AS DEST
WHERE
    1 = DEQP.query_plan.exist(
        '//RelOp[
            @PhysicalOp = "Hash Match"
            and @LogicalOp = ("Aggregate","Partial Aggregate","Flow Distinct")]')
    AND DEST.[text] LIKE N'%GROUP BY%';

This might miss some GROUP BY queries if they have a different amount (or type) of white space between GROUP and BY than expected by the query. Maybe replacing all white space with spaces and collapsing contiguous ones into a single space could be done before the LIKE or use SQLCLR and regex instead.

You would need to inspect the results by hand to determine if the Hash Match Aggregate correlates directly to the GROUP BY clause or not. The query above could be extended, perhaps, to check the plan HashKeysBuild element against the grouping columns, but this would be hard to get right, due to optimizer operations.


Why does pgAdmin3 set OIDS=FALSE and owner to postgres by default?

1 Why oids=false?

OIDS=FALSE/TURE,  the default setting depends upon the default_with_oids configuration parameter，The parameter is off by default。in PostgreSQL 8.0 and earlier, it was on by default.

2 Why ownered by postgres?

Please check the role you use log in  when using  pgAdmin3 ， if you log in as postgres role ,then  the owner of  new created table will be the postgres role.


MongoDB doesn't start after changing the data directory

I ran into a similar issue and found it to be a misconfigured mongod.conf file in my case.  It could also be the permissions on the new directory are not set properly.  chown -R mongod:mongod <directory name> was how I ensured access (and of course the chmod 600 <dir> as well).  Lastly, run an ls -Z to ensure the context is correct.  I just compared to the default directory which worked for me.

If this is not resolved already, please show the contents of your log file as well.  There may be some clues there.


Take a look at Comparison of database tools, there you find some tools supporting both HSQL
 and MySQL. Then it should be possible to export the MySQL DB and directly import to HSQL DB from the same GUI.


Should I drop MS_DataCollectorInternalUser in msdb?

MS_DataCollectorInternalUser is not an orphaned user. Is a user without a login, a different kind of beast. I suggest you update your security practices, you may have already dropped other valid non-orphaned users. Unless you have a specific security bulletin quoting a reason to drop or modify this user you should leave it in place.


There is no direct way of doing it in SybaseASE (as opposed to sql server - which exposes DMV data).

You can get close to see if your UPDATE INDEX STATISTICS is getting CPU, Physical IO and is not being blocked.

I use below query :

select l.spid
    ,p.status as status
    ,Db_name(l.dbid) as dbname
    ,p.hostname as hostname
    ,p.program_name as program_name
    ,substring(Object_name(l.id, l.dbid), 1, 30) as object
    ,p.blocked
    ,case
        when type = 1
            then "Exclusive table lock"
        when type = 2
            then "Shared table lock"
        when type = 3
            then "Exclusive intent lock"
        when type = 4
            then "Shared intent lock"
        when type = 5
            then "Exclusive page lock"
        when type = 6
            then "Shared page lock"
        when type = 7
            then "Update page lock"
        when type = 8
            then "Exclusive row lock"
        when type = 9
            then "Shared row lock"
        when type = 10
            then "Update row lock"
        when type = 11
            then "Shared next key lock"
        when type = 256
            then "Lock is blocking another process"
        when type = 512
            then "Demand lock"
        end as "TYPE_OF_LOCK"
    ,l.page "Page"
    ,Substring(p.cmd, 1, 16) as executing_command
    ,p.physical_io as "PHYSICAL_I/O"
    ,p.cpu as cpu
from master..syslocks l
    ,master..sysprocesses p
where l.spid = p.spid


generate_series not working in single user mode

The INSERT ... VALUES (...), (...), ... ; syntax can accept multiple rows but you can't put a table generating function inside a row.

You need to use the INSERT .. SELECT .. FROM .. ; syntax and put the table generating function in the FROM clause:

insert into t1 (i, j)
select g.i, g.i
from generate_series(1, 200000) as g(i) ;


First of all, with innodb_file_per_table=ON, all data and index pages are stored in the .ibd file of the table.

The growth of the system tablespace (ibdata1) can be a big headache.

I have mentioned this in earlier posts

Apr 23, 2013 : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full
Jun 16, 2014 : MySQL Index creation failing on table is full

In your case, there is transaction overhead. You have big transactions because of the mysqldump. Why ?

The default option called --opt has many options enabled, including --extended-insert. Extended inserts will cause mysqldump to INSERT INTO multiple rows in batches. Each batch of rows is a single transaction. Thus, you should expect ibdata1 to get bloated, especially if the table has TEXT or BLOB data (I suspect that since you have fulltext indexes).

The only way around this would be to run the mysqldump again with --skip-extended-insert. This will do three(3) very nasty things:

Runs INSERT for each row
Makes a much larger mysqldump file
Take a ton longer to reload

This makes each individual INSERT transaction hundreds or thousands of times faster because you are inserting one row instead of hundreds or thousands. Unfortunately, you are doing hundreds or thousands longer to execute because of the number of INSERTs. This is just one way to prevent ibdata1 from getting bloated.

Another thing you could try would be to drop the FULLTEXT index from the table, mysqldump it with skip-extended-insert, reload it, then add the FULLTEXT index back. This could help because the adding the FULLTEXT index is not a transaction.

Give it a Try !!!


The a1 alias belongs to a table to which the a2 derived table is being joined, and you are trying to reference the alias from inside the derived table. You cannot do that in SQL. This is not just a limitation of MS Access.
If it was a correlated subquery and the alias in question was assigned at any of the outer levels, such a reference would be valid, but a derived table is not allowed to reference aliases of any of the tables it is being joined to. You may reference them in the derived table's ON joining predicate. In this case, a1.Id is a valid reference in ON a1.Id = a2.lClientId, and it would be equally valid if it was used in a correlated subquery in the ON clause, for instance:
ON EXISTS
  (
    SELECT
      ... something something ...
      ... a1.Id ...
      ...
  )

That would work.  But such references cannot be resolved inside the derived table itself. As a result, you cannot reference a1 inside a2.


Much simpler: CONVERT(varchar(8),GETDATE(),112)

You can find a full list of CONVERT styles in Sybase BOL, although the explanation of each style isn't particularly clear (and is actually flat wrong for style 12 & 112). The Complete Sybase ASE Reference Guide includes a full list of styles with examples. (Please note that the complete guide is externally published, not from Sybase. Also I'm not affiliated with the book or the publisher, just mentioning it because everyone working with ASE should have a copy of it.)


Splitting a large table to improve performance

My first question to your situation would be if the Standard Edition is a given or if you had the possibility to upgrade to Enterprise.

Within the Enterprise Edition of SQL Server 2008 R2 you would have the possibility to use Partitioned Tables.

Essentially, they do what you described as a manual process in the background.

Quote from 1:

  Partitioning makes large tables or indexes more manageable, because partitioning enables you to manage and access subsets of data quickly and efficiently, while maintaining the integrity of a data collection. By using partitioning, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes in earlier versions of SQL Server. Maintenance operations that are performed on subsets of data are also performed more efficiently because these operations target only the data that is required, instead of the whole table.

EDIT as op stated that Enterprise is not an option:

When using Standard Edition, you can fall back to using Partitioned Views.

The main difference from the usage side is that you have to maintain the partitioned tables for yourself and you also have to make sure that every new member tables gets added into the view.

The idea builds on your scenario 2 but you don't have to care about where to insert the data, as this is determined by the Partitioned View based on CHECK-Constraints in the member tables.

For a good example, see 2

As for all partitioning scenarios you have to make sure that the partitioning columns you choose fit your query-and-insert behaviour.


Alternative to Dynamic SQL inside trigger?

If the list things to check is constant, then spell them out rather than constructing the tests. (not viable)

But, presumably that is not the case...

Plan A: Replace the INSERT that needs to call the trigger with a PROCEDURE call.  The routine contains the INSERT plus the rest of the code you presented.

Plan B: As with Plan A, but do it in application code.

Note:  Either A or B would be wrapped in BEGIN...COMMIT to make it 'atomic'.

Bottom Line:  Triggers can't do everything.

Enforcement of A (I think this will prevent other from bypassing the stored proc.)

CREATE USER special@... ...  -- a new user
GRANT INSERT ON db.tbl TO special@...  -- let it get in
The stored proc would be SECURITY special@...  -- to run proc as "special" so it can do the INSERTs
Remove INSERT PRIVILEGE for that table from others.  (This gets messy, or maybe unnecessary.)  (It may help if the table is in a separate database that no one currently has access to.)
(My gut says there is one more step. I'll leave it as a 'exercise for the reader'.)


Design critique on a table to keep track of which person flipped a switch at what time

The table should have a primary key - you can add an auto increment field as an alternative.
I would create a table Person to store persons (store the name here) and a table Organization to store organizations. Given that, I would store person_id instead of name and I would store organization_id instead of organization. I assume that the same person might flip the switch more than once. If that is not the case, then perhaps your design works as well. If the persons are employees of an organization then you don't need the organization/organization_id field, you need another table where you store the employment relationship.
I would add indexes on the two FK fields added at #2 and perhaps the changed_at field, this depends on how you want to search the table, but at the very least you need to add an index for each FK field.
In your post there is no information about the switch that was flipped. Does it matter? Do you have a list of switches? If you do, you might want to add to this table a switch_id to keep track of which switch was flipped. Again, only if this information matters.

Re #2, the design can vary, depending on how flexible you want it to be to accommodate future requirements. You can go nuts with it and define entities that can model organizations, divisions, business units etc. if you need to. Does it matter? It depends on the requirements and what kind of information you want to extract from this table. Do you want to classify the people that flip the switch? Then you need to add more tables. For now I would keep it as simple as possible given the current requirements and with an eye on the future requirements but don't overdo it.


Is it possible to change the color of relationship lines in MySQL Workbench diagrams?

I found a solution to help. Close Workbench. Change the theme of your mac in clear mode. Restarts Workbench and saves again in pdf.