texts: How to access Multiple DB2 Relational Databases In A Single Query

texts:
I am looking for a solution to the problem described below.
There are two DB2 instances with two authorization IDs on the same server and can be accessed using the same user User1.
Example:
To access Inst1DB, I use the following commands:
[User1 ~]$ . /db2/authID1db2/home/authID1db2/sqllib/db2profile
[User1 ~]$ db2 connect to Inst1DB

To access Inst2DB, run the following commands:
[User1 ~]$ . /db2/auth2db2/home/auth2db2/sqllib/db2profile
[User1 ~]$ db2 connect to Inst2DB

Note that one needs to terminate the current DB2 session before connecting to another DB instance.
To terminate DB session, run the following command:
[User1 ~]$ db2 terminate

Issue:
I want to join two tables, one of which belongs to INST1DB and the other to INST2DB, while connected to INST1DB.
[User1 ~]$ . /db2/authID1db2/home/authID1db2/sqllib/db2profile
[User1 ~]$ db2 connect to Inst1DB
[User1 ~]$ db2 "Select * from Inst1DB.schema1.table1 T1 , Inst2DB.schema2.table2 T2 where T1.column1 = T2.column1.

Curerntly I am getting below error:

SQL0204N  "Inst2DB.schema2.table2" is an undefined name.  SQLSTATE=42704.

Query:
Can anyone suggest to me how to connect the two instances and perform a join on these tables?


texts: I have a database backup (.bak) from SQL Server v7, how do I convert it for load into v10?

texts:
A client has given me a backup (.bak) from a SQL Server v 7 database.  I am trying to load it into version 10 which is incompatible.  Are there any tools that will restore or prepare an old version backup to be restored into version 10?

EDIT: Per request, here is the text from the error dialog

  The database was backed up on a server running version 7.00.1063. That version is incompatible with this server, which is running version 10.00.1600. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3169)


texts:
I have two tables.

One of them TABLE1 has values like this:

TABLE1

|A   |B   |C   |D   |E   |F   |G   |H   |
-----------------------------------------
|1   |NULL|4   |NULL|NULL|NULL|NULL|10  |

TABLE2 has more values compared to TABLE1.

|A   |B   |C   |D   |E   |F   |G   |H   |
-----------------------------------------
|1   |2   |3   |NULL|5   |NULL|6   |11  |

I need to update TABLE1 with values from TABLE2 for every column with NULL values on TABLE1.

The result that I need:

|A   |B   |C   |D   |E   |F   |G   |H   |
-----------------------------------------
|1   |2   |4   |NULL|5   |NULL|6   |10  |


texts: MS SQL Server merge replication cannot find file specified

texts:
I'm trying to setup merge replication between two MS SQL Server 2012 databases on different hosts in one domain.

When replication is set up and first snapshot is generated replications starts. Replication monitor shows that tables are being prepared and bulk copied, but after about 40 minutes i get messages like

The process could not bulk copy into table "table_name".
...
The system cannot find the file specified.

The table is different every time. I'm trying to locate these missing files but they are absent. When i just sit and monitor these folders - i can see some folders being deleted a few moments before the error occurs. Tried both push and pull with similar results.

Answers on the web show it may be caused by

User having insufficient privileges. I suppose this one is wrong because data is replicated till the error occurs - i can see tables being cleaned and filled with replicated data.
Using local path for replication files. I'm using UNC path located on Publisher. Both publication and subscription occur with windows domain account with sufficient permissions.

Replication silently restarts and subscriber is constantly in inconsistent state.

Does anyone have any idea why this may happen or how can i debug the problem?


texts: Which type of repair level is “DBCC CHECKDB (databasename, repair)”?

texts:
The MSDN's article about the command "DBCC CHECKDB" explains three ways to perform a database repair in the syntax section:

- REPAIR_ALLOW_DATA_LOSS
- REPAIR_FAST
- REPAIR_REBUILD

But I found the following statement when I was looking for how to repair a suspect (I put in this mode doing these steps) database and I can't understand which one of the three modes is it:

  DBCC CHECKDB (databaseName, repair)

I executed the statement and it works fine. I'm confused because there isn't any reference to the "repair" parameter alone without finish the word with "_allow_data_loss", "_fast" or "_rebuild".

If I try to run "DBCC CHECKDB (databaseName, repair_rebuild)" on my database in emergency status, I receive a error message saying I can't execute that level of repair while the database is in emergency status. So, I discard that "repair" alone is "repair_rebuild".

That statement is the third line in the script that I found:

ALTER DATABASE databaseName SET EMERGENCY;
ALTER DATABASE databaseName SET SINGLE_USER;
DBCC CHECKDB (databaseName, repair) ALL_ERRORMSGS
ALTER DATABASE databaseName SET MULTI_USER;
ALTER DATABASE databaseName SET ONLINE;

If I read the output of the third statement execution I see information about the process but nothing related on the level used in the repair.

Thanks in advance,


texts: Why does postgres choose to sort instead of scanning the index?

texts:
I have the following table:

# \d service
       Table "public.service"
   Column    |   Type   | Modifiers
-------------+----------+-----------
 customer_id | integer  | not null
 date        | date     | not null
 service     | smallint | not null
 has         | boolean  |
Indexes:
    "service_customer_id_idx" btree (customer_id)

# select count(*) from service;
   count
-----------
 327535416
(1 row)

Time: 75047.508 ms

# select version();
                                                   version

-------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.5.4 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 6.1.1 20160621 (Red Hat 6.1.1-3), 64-bit
(1 row)

I tried to come up with a query for which it is obviously beneficial to use the
index, since the results can be taken directly from the index in the correct
order:

# explain (analyze,verbose) select customer_id from service order by customer_id;
                                                               QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=64804990.92..65623829.48 rows=327535424 width=4) (actual time=487209.250..546557.064 rows=327535416 loops=1)
   Output: customer_id
   Sort Key: service.customer_id
   Sort Method: external merge  Disk: 4482448kB
   ->  Seq Scan on public.service  (cost=0.00..5045816.24 rows=327535424 width=4) (actual time=2.705..49659.173 rows=327535416 loops=1)
         Output: customer_id
 Planning time: 0.093 ms
 Execution time: 554914.731 ms
(8 rows)

Time: 554919.649 ms

As you see, Postgres prefers to do a sequential scan and then sort the results.

Interestingly, if I add a limit clause, it does decide to use the index:

# explain (analyze,verbose) select customer_id from service order by customer_id limit 10;
                                                                             QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..32.97 rows=10 width=4) (actual time=2.509..2.773 rows=10 loops=1)
   Output: customer_id
   ->  Index Only Scan using service_customer_id_idx on public.service  (cost=0.57..1061141647.19 rows=327535424 width=4) (actual time=2.503..2.760 rows=10 loops=1)
         Output: customer_id
         Heap Fetches: 10
 Planning time: 4.285 ms
 Execution time: 2.906 ms
(7 rows)

Time: 28.178 ms

Why does Postgres behave this way, and how could I debug this? Is there a way to
ask it to show alternative plans and their cost calculations?

Here's the execution with seqscan turned off:

# explain (analyze,verbose) select customer_id from service order by customer_id;
                                                                                QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using service_customer_id_idx on public.service  (cost=0.57..1061141647.19 rows=327535424 width=4) (actual time=2.753..346400.896 rows=327535416 loops=1)
   Output: customer_id
   Heap Fetches: 327535416
 Planning time: 1.921 ms
 Execution time: 355637.985 ms
(5 rows)

Time: 355647.367 ms


texts: Writing a view that caches to another table

texts:
I have a table market_trades with columns timestamp, market_id, and amount:

api_production=# \d market_trades;
                                       Table "public.market_trades"
     Column     |            Type             |                         Modifiers
----------------+-----------------------------+------------------------------------------------------------
 id             | integer                     | not null default nextval('market_trades_id_seq'::regclass)
 market_id      | integer                     | not null
 timestamp      | timestamp without time zone | not null
 amount         | numeric(16,8)               | not null

And I just created a table market_trades_sum_cache:

api_production=# \d market_trades_sum_cache;
                                       Table "public.market_trades_sum_cache"
     Column     |            Type             |                         Modifiers
----------------+-----------------------------+------------------------------------------------------------
 id             | integer                     | not null default nextval('market_trades_sum_cache_id_seq'::regclass)
 market_id      | integer                     | not null
 start_time     | timestamp without time zone | not null
 end_time       | timestamp without time zone | not null
 sum            | numeric(16,8)               | not null

I want to write a view (or some other type of reusable function) called trade_volume that takes a market_id, start_time, and end_time as parameters and does the following:

If there is a row in market_trades_sum_cache with the given market_id, start_time, and end_time, return the corresponding sum.
Otherwise, add up the amounts of all rows in market_trades with the given market_id and whose timestamp is between start_time and end_time, store that sum as a new row in market_trades_sum_cache, and return the sum.

Is this possible in PostgreSQL 9.3?

BONUS: Use PostgreSQL's timestamp range types (tsrange in PostgreSQL 9.2+) instead of start_time and end_time in market_trades_sum_cache.


texts: Changing the SQL Server Compatibility setting for individual tables/columns, which have retained older settings

texts: Is there a clear and methodological method to change the compatibility mode of a databases individual Tables & Columns?  A 3rd party database was upgraded and migrated to SQL 2005, the SQL Server Compatibility is at the correct setting, but there are tables/columns contained within the new db that have retained old compatibility settings.


texts: Possible career paths for Oracle Certified Associate (OCA)

texts:
In order to be confident in databases as a entry level job candidate, I completed the OCA certification.  Now I want to know what career paths follow this.  Can one go into data-mining,  BI, or R&D via this certification?

And I heard DBA is quite boring and risky for experimental people.


texts: MySQL ERROR 1137 (HY000) at line 9: Can't reopen table: 'temp_table'

texts:
I'm receiving an error ERROR 1137 (HY000) at line 9: Can't reopen table: 'temp_table' when executing a query similar to the following:

USE database_name;

CREATE TEMPORARY TABLE temp_table (status varchar(20));
INSERT INTO temp_table (status)
    SELECT status
    FROM client_contractor_status
    WHERE type = 'process';

SELECT table1.col1,
       table1.status,
       (SELECT
            COUNT(*)
        FROM
            table2
        RIGHT OUTER JOIN
            temp_table
            ON table2.status = temp_table.status
        WHERE table2.col1 = table1.col1
        ) AS counter
FROM
    table1

RIGHT OUTER JOIN
    temp_table
    ON table1.status = temp_table.status

I'm (just about) aware of the limitation that says you can't access a temporary table by two different aliases, however I haven't aliased it at all.

Is it being aliased automagically because it's in a sub-query? And if so, how can this be fixed?

Thanks.


texts: Only Filtered condition gets deleted from index after datatype change using SSMS

texts:
In SSMS if I create a filtered index we can see filtered condition in index properties. But if I change column type of a column involved in filtered index using SSMS , then after column type changes filtered index condition will be deleted .

For example i created a filtered index for column of datatype varchar(50) . I changed datatype to varchar(100) by using SSMS. After changing datatype I check index properties for that column. Now only filtered condition was missing in properties.

Then I tried to check what may be the reason behind this error. Now when I changed datatype I used "save change script window" and checked what SSMS really does. As we all know the SSMS recreates table index etc , but the index creation query here was missing the filtered condition. Below is part of "save change script window" query showing index creation which does not have filtered condition!!

CREATE NONCLUSTERED INDEX filtered ON dbo.Table_1
(
t
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

So is this bug in SSMS? Or is there anything i am missing like settings in SSMS or anything else?


texts: MySQL SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded

texts:
I have database session enable, when website got thousands of visitor. It gives me error

[30-Apr-2014 04:27:12 America/Denver] PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded;
 try restarting transaction' in public_html/lib/Cake/Model/Datasource/DboSource.php:458
Stack trace:
#0 public_html/lib/Cake/Model/Datasource/DboSource.php(458): PDOStatement->execute(Array)
#1 public_html/lib/Cake/Model/Datasource/DboSource.php(424): DboSource->_execute('UPDATE `yuldi_d...', Array)
#2 public_html/lib/Cake/Model/Datasource/Database/Mysql.php(400): DboSource->execute('UPDATE `yuldi_d...')
#3 public_html/lib/Cake/Model/Model.php(1821): Mysql->update(Object(cake_sessions), Array, Array)
#4 public_html/lib/Cake/Model/Datasource/Session/DatabaseSession.php(117): Model->save(Array)
#5 [internal function]: DatabaseSession->write('66336365f5fc91c...', 'Config|a:3:{s:9...')
#6 [internal function]: session_write_close()
#7 {main}
  thrown in public_html/lib/Cake/Model/Datasource/DboSource.php on line 458

I want to know, how can i check MySQL insert queries per second, is there any commands to check that using ssh ?
is there changes in my.cnf required ?


texts: Does Active Data Guard include Golden Gate?

texts:
I've been told that Active Data Guard incorporates Golden Gate as part of its technology stack.

Is Golden Gate usable directly from the Active Data Guard installation?


texts:
This next year, I am helping an effort to clean several SQL Server environments.

We have about 10,000 stored procedures and estimate that only about 1000 of them are used on a regular basis, and another 200 or so are used on a rare occasion, meaning we have a lot of work to do.

Since we have multiple departments and teams that can access these databases and procedures, we are not always the ones calling the procedures, meaning that we must determine what procedures are being called.  On top of that, we want to determine this over a few months, not in a few days (which eliminates some possibilities).

One approach to this is to use the SQL Server Profiler and track what procedures are being called and compare them to the list of what procedures we have, while marking whether the procedures are used or not.  From then, we could move the procedures to a different schema in case a department comes screaming.

Is using the Profiler the most effective approach here?  And/Or have any of you done something similar and found another way/better way to do this?


texts: Query single column, all rows in Postgres 2D Array

texts:
Is there any way to fix the query below so I don't have to specify an arbitrary max row count?

DROP TABLE IF EXISTS array_test;

CREATE TABLE array_test (
  id serial primary key,
  name text,
  array_column integer[][]
);

insert into array_test (name, array_column) VALUES
  ('rowa', ARRAY[[1,2,3],[4,5,6]]),
  ('rowb', ARRAY[[4,5,6],[7,8,9],[10,11,12]]);

This does what I want, but I don't like hard-coding 9999 as an arbitrary max row count:

select array_column[1:9999][2:2] from array_test;


texts: Migrating data to new mysql rds instance

texts:
I have 1TB size mysql database for production . Now i want to migrate this database on new mysql RDS instance .
Please suggest me which backup policy should i apply here like Mysqldump,XtraBackup . ?


texts: are overlapping multi column statistics redundant?

texts:
I have run a database engine tuning advisor session, DTA, to optimize my database.

The DTA has produced some statistics recommendations, however, I noticed some are contained in others for example on my table ProductIcon below:

    CREATE STATISTICS [_dta_stat_172123954_1_2_3_4_5_12]
ON [dbo].[ProductIcon]
([ClassID], [SegmentID], [GroupID], [Tier1], [LanguageID], [Active])
    go

    CREATE STATISTICS [_dta_stat_172123954_11_1_2_3_4_5_12]
ON [dbo].[ProductIcon]
([IconTypeID], [ClassID], [SegmentID], [GroupID], [Tier1], [LanguageID], [Active])
    go

this is the table definition:

    IF OBJECT_ID('[dbo].[ProductIcon]') IS NOT NULL
    DROP TABLE [dbo].[ProductIcon]
    GO
    CREATE TABLE [dbo].[ProductIcon] (
    [ClassID]      INT                              NOT NULL,
    [SegmentID]    INT                              NOT NULL,
    [GroupID]      INT                              NOT NULL,
    [Tier1]        VARCHAR(10)                      NOT NULL,
    [LanguageID]   SMALLINT                         NOT NULL  CONSTRAINT [DF_ProductIcon_LanguageID] DEFAULT ((1)),
    [Image]        VARCHAR(50)                      NOT NULL  CONSTRAINT [DF_ProductIcon_Image] DEFAULT (''),
    [Descr]        VARCHAR(100)                     NOT NULL  CONSTRAINT [DF_ProductIcon_Descr] DEFAULT (''),
    [SortOrder]    INT                              NOT NULL  CONSTRAINT [DF__ProductIc__SortO__6A358A53] DEFAULT ((1)),
    [URL]          NVARCHAR(512)                        NULL,
    [LinkJSClick]  NVARCHAR(512)                        NULL,
    [IconTypeID]   INT                                  NULL,
    [Active]       BIT                              NOT NULL  CONSTRAINT [DF_ProductIcon_Active] DEFAULT ((1)),
    [Tier2]        VARCHAR(10)                      NOT NULL  CONSTRAINT [DF_ProductIcon_Tier2] DEFAULT (''),
    CONSTRAINT   [PK_ProductIcon]
PRIMARY KEY CLUSTERED
([ClassID] asc, [SegmentID] asc, [GroupID] asc,
 [Tier1] asc, [LanguageID] asc, [SortOrder] asc, [Tier2] asc))

Question:

If I knew exactly how the stats would be used I would probably be able to decide whether or not I should keep both or one of them.

Is there any way I can have this vision so that I can make my decision as to which one(s) of them I should keep?

we are still on sql 2005 for this one.

This table currently does not have any other index , however the following index is appointed as missing:

CREATE NONCLUSTERED INDEX IDX_IconTypeID_Active_INC_1_2_3_4_5_6
ON [dbo].[ProductIcon] ([IconTypeID], [Active])
INCLUDE (ClassID,SegmentID,GroupID,Tier1,Descr,Tier2)


texts: Joining on a field with different values

texts:
I am trying to join data from two completely different sources. One source contains an employee's schedule information, and the other tracks what they actually worked (like what time they actually took lunch or break). The problem is, the schedule program gives times as BREAK1, BREAK2, BREAK3, and LUNCH, while the tracking program simply lists them as Lunch and Break. I can join the data and get the lunches just fine, but the breaks are throwing me off. If I convert BREAK1, BREAK2, and BREAK3 to just "Break", I end up with too many segments because it is matching every instance with every other instance. Is there a way that anyone can think of to join these two pieces of information?

Here is some sample data:

This is the Scheduled Times:

EMP_ID  NOM_DATE    SEG_CODE    START_MOMENT    STOP_MOMENT
626009  26-Sep-13   BREAK2          9/26/13 5:00 PM 9/26/13 5:15 PM
625650  26-Sep-13   BREAK2          9/26/13 4:30 PM 9/26/13 4:45 PM
638815  26-Sep-13   BREAK2          9/26/13 4:00 PM 9/26/13 4:15 PM
621649  26-Sep-13   BREAK2          9/26/13 3:30 PM 9/26/13 3:45 PM
567005  26-Sep-13   BREAK2          9/26/13 3:30 PM 9/26/13 3:45 PM
626009  26-Sep-13   LUNCH           9/26/13 2:30 PM 9/26/13 3:30 PM
625650  26-Sep-13   LUNCH           9/26/13 1:30 PM 9/26/13 2:30 PM
638815  26-Sep-13   LUNCH           9/26/13 1:30 PM 9/26/13 2:30 PM
621649  26-Sep-13   LUNCH          9/26/13 12:30 PM 9/26/13 1:30 PM
567005  26-Sep-13   LUNCH          9/26/13 12:30 PM 9/26/13 1:30 PM
626009  26-Sep-13   BREAK1         9/26/13 11:45 AM 9/26/13 12:00 PM
625650  26-Sep-13   BREAK1         9/26/13 11:30 AM 9/26/13 11:45 AM
638815  26-Sep-13   BREAK1         9/26/13 11:45 AM 9/26/13 12:00 PM
621649  26-Sep-13   BREAK1         9/26/13 9:30 AM  9/26/13 9:45 AM
567005  26-Sep-13   BREAK1         9/26/13 9:30 AM  9/26/13 9:45 AM

This is the Actual Times

EMP_ID  Seg_Code    Start_Time  Stop_Time
625650  Break           9/26/2013 17:54 9/26/2013 17:55
567005  Break           9/26/2013 14:56 9/26/2013 14:59
567005  Break           9/26/2013 15:32 9/26/2013 15:44
638815  Break           9/26/2013 16:34 9/26/2013 16:47
567005  Break           9/26/2013 10:08 9/26/2013 10:21
626009  Break           9/26/2013 17:01 9/26/2013 17:15
625650  Break           9/26/2013 11:31 9/26/2013 11:45
626009  Break           9/26/2013 11:52 9/26/2013 12:07
621649  Break           9/26/2013 9:34  9/26/2013 9:48
621649  Break           9/26/2013 15:31 9/26/2013 15:45
638815  Break           9/26/2013 11:46 9/26/2013 12:02
625650  Break           9/26/2013 16:35 9/26/2013 16:51
567005  Lunch           9/26/2013 12:31 9/26/2013 13:29
625650  Lunch           9/26/2013 13:31 9/26/2013 14:30
626009  Lunch           9/26/2013 14:31 9/26/2013 15:30
638815  Lunch           9/26/2013 13:31 9/26/2013 14:30
621649  Lunch           9/26/2013 12:31 9/26/2013 13:30

I am trying to get the difference (in minutes) between when they are scheduled, and when they are actually taking breaks. A correct example is:

Badge   Seg_Code    Scheduled Start     Scheduled Stop      Actual Start           Actual Stop      Difference      Seg_Duration
192329  Lunch       9/26/13 8:15 AM     9/26/13 9:15 AM     9/26/2013 8:18:27 AM    9/26/2013 9:17:59 AM        3       0:00:59:32

Thank you


texts: How to check the last time any table in database modified in ms sql server

texts:
I have around 5 database and I am taking daily backup of all 5 database. I know that out of that 5 database 1 of the database is not in use and we are still taking the backup of that database.

My question is how we can check if the database is in used, like how can I verify when was the last time any Insert,update or delete happening in the database.

I think which I think of I can check the database size for 1 week and If the size of the database is same that means it is not in used. But I want to check when was the last time any DML command run.


texts:
I had Oracle 10G XE installed on my Windows machine. The machine got corrupted and I will have to install Oracle again. I did not take a backup in the form of SQL queries.

How can I use the old database with the new installation?


texts: Oracle server session terminated by fatal error

texts:
I have an Oracle server running on Windows Server.

When an INSERT statement runs I receive an Oracle Ora-00603 error.

The insert statement just tries to insert a BLOB into a table.  I searched the error specification on Google and found that perhaps there are network connection issues. The suggested action is to log in again to Oracle Server. I followed this tip, however I still receive the same error.

The error I receive is the following:

  ora-00603: ORACLE server session terminated by fatal error

I am using an Oracle 10 client. My application is a C# application. Any suggestions?


texts: Naming convention for columns with same name (but different logically)?

texts:
How do I name columns in my database tables which have the same name in outside world but are logically separated?

Lets say an example

Company Share:   100% of 50%

Now I have two company shares: 100 percent and 50 percent.

I could rename them as CompanyShare1, CompanyShare2 but programmers have a hard time  understanding these names. Is there any tip on dealing with such scenarios?


texts: pt-table-checksum to compare two tables?

texts:
I'm trying to find a way of comparing two tables in two databases (master and slave), as I have applied some slightly different settings to one, and need to see what affect it has (if any) on the data etc.

Ordinarily I would use MySQL Utilities dbcompare which has worked well in the past, but this doesn't seem to have the option for a single table, and mysqldiff is too limited.

So I downloaded percona-toolkit to my Debian Server (the slave) and am looking at pt-table-checksum. But the Page on the Percona website is very little help, instead filled with all manner of options, but seemingly no examples.

I set up a test schema, with one table, and diffent data.

So far I have got as far as:

pt-table-checksum h=1.2.3.4,P=3306,u=xxxx,p=xxxx, -h1.2.3.5 --databases=test --tables=t1 --no-check-binlog-format

(apparently there is some problem with Row Based Replication [?] hence --no-check-binlog-format)

But this returned:

# A software update is available:
#   * The current version for Percona::Toolkit is 2.2.14.

TS             ERRORS  DIFFS     ROWS  CHUNKS SKIPPED   TIME  TABLE
08-17T09:54:32      0      1        4       1       0   0.291 test.t1

Not exactly what I had in mind. Am I missing something, or have I completely misunderstood what pt-table-checksum does?

I'm not sure why I didn't get the latest version either. I ran sudo apt-get update followed by sudo apt-get install percona-toolkit. (as you may have gathered, I'm not really a Linux person)


texts: Improve speed of stored proc with lots of inserts [Azure-SQL]

texts:
Running an S0. @@version = Microsoft SQL Azure (RTM) - 12.0.2000.8   Jul 21 2017 14:10:34   Copyright (C) 2017 Microsoft Corporation. All rights reserved.
The stored proc uses 3 tables. One main object with a relation to prices and a relation to features.

I have about 10.000 main objects with per piece 10 features and avg 3000 prices. So that will cause the prices table to have 30 million rows.

I have a C# console app which runs my stored proc per mainobject and have speed up price and feature storage by using types and table values. I have a dictionary in my app with mainobject code and id so I can fill the id of items which do not need an insert. So that I can preserve the primary key, since they don't change much. I had 3 stored procedures at first. One per table, but instead of 3 stored proc executions, I moved to an all in one sp.
Here is my SQL code:

ALTER PROCEDURE [dbo].[OneSpToRuleThemAll]
(
    @code nvarchar(max),
    @name nvarchar(max),
    @lat float,
    @lng float,
    @country int,
    @operator int,
    @id int output,
    @dtFeatures as FeatureType READONLY,
    @dtPrices as PriceType READONLY
)
AS
BEGIN
    SET NOCOUNT ON
-- begin stored proc 1:
    IF (@id > 0)
    begin
        UPDATE MainObject SET
            [Name] = @name,
            Latitude = @lat,
            Longitude = @lng,
            CountryId = @country,
            OperatorId = @operator,
        WHERE code = @code;
    end
    else
    begin
        INSERT INTO [dbo].[MainObject]
           ([Code]
           ,[Name]
           ,[Latitude]
           ,[Longitude]
           ,[CountryId]
           ,[OperatorId])
     VALUES
           (@code, @name, @lat, @lng, @country, @operator);
            select @id = SCOPE_IDENTITY();
    end
-- begin stored proc 2:
    delete from features where MainObjectId = @id;
        INSERT INTO [dbo].[Features]
           ([ExternalId]
           ,[Mandatory]
           ,MainObjectId])
     select [ExternalId]
           ,[Mandatory]
           ,MainObjectId] from @dtFeatures;
-- begin stored proc 3:
    delete from prices where MainObjectid = @id;
    INSERT INTO [Prices]
        ([From]
        ,[To]
        ,[PriceInclDiscount]
        ,[PriceExclDiscount]
        ,[MainObjectId])
    select [From]
        ,[To]
        ,[PriceInclDiscount]
        ,[PriceExclDiscount]
        ,[MainObjectId] from @dtPrices;

END

edit as @S4v1n suggested, I had to post the execution plan. I used https://stackoverflow.com/a/4146573/169714 this code to get the full SQL command and pasted that in SSMS and got this queryplan for one of the mainobjects:

Here is the execution plan of the 2nd stored proc:


texts: Row count from a certain table in multiple databases

texts:
I need to know the row count of a certain table that exists (or don't) in multiple databases.

Here's what I came with:

DECLARE @NomeBase as char(60)
declare @base as char(100)
declare @select as char(1000)

DECLARE CBases CURSOR FOR  select databasename from sig_orgao_web
 --gets all my databases
OPEN CBases
FETCH NEXT FROM CBases INTO  @NomeBase

WHILE (@@FETCH_STATUS = 0)
  BEGIN
    set @base =rtrim(@NomeBase) + '.dbo.sia_pais' -- database.dbo.table format
    select @base
    set @select = 'SELECT cast(  count(*) as char(70)) from ' + @base
    exec (@select)

    FETCH NEXT FROM CBases INTO  @NomeBase
  END
CLOSE CBases
DEALLOCATE CBases

I know that tablenames have to be static, so I'm creating the select statements dynamically. The results are like this:

SIIG_DAT_DESENV.dbo.sia_pais
13
SIIG_DAT_HOMOLOGACAO.dbo.sia_pais
13

And it goes on.

The thing is, if the table doesn't exist in my database or if the user which is running this script on management studio doesn't have access to a certain database, an error will be thrown.

Is there a better way to do this?


texts:
I am having a problem with one of my MySQL servers running CentOS 7.  I have it almost setup with a master master replication status.  But I keep getting this:

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.220.80
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000037
          Read_Master_Log_Pos: 245
               Relay_Log_File: mariadb-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000037
             Slave_IO_Running: Connecting
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 245
              Relay_Log_Space: 245
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master 'replication@192.168.220.80:3306' - retry-time: 60  retries: 86400  message: Access denied for user 'replication'@'asm-sql01.borg.priv' (using password: YES)
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
1 row in set (0.00 sec)

MariaDB [(none)]>


texts:
I want to capture truncate and drop operations on my table.

So i executed the following:

SQL> select name, value from v$parameter where name like '%aud%';

NAME           VALUE
----           -----
audit_trail    DB

SQL> create table audit_test (id number);

Table created.

SQL> audit all on audit_test by access;

Audit succeeded.

SQL> create table audit_test (id number);

Table created.

SQL> audit all on audit_test by access;

Audit succeeded.

SQL> truncate table audit_test;

Table truncated.

And the truncate does not exists in the dba_audit_Trail...

Thanks for help.


texts: Optimize Oracle 11g SQL*Plus connection for large remote SELECTs

texts: Which options should I set in SQL*Plus for a script that runs multiple SELECTs, (some are JOINs) to SPOOL the results as text (CSV) in order to optimize these SELECTs, specifically one resulting in a 50 MB file, so that they take shorter to run, while the connection runs over the Internet? There's only read-only access, so I'm wondering what the options may be.


texts:
I'm creating a view in which I have a where with an in clause and I want it to be a parameter. Unfortunately views can't be created with parameters, so I tried replacing it with functions, but functions only return a string.

Is there a way to create a view that accepts multiple options? example:

SELECT name, surname from people WHERE name in (@names)

I'm using MySQL 5.5


texts:
I have a table with a column with the account IDs and I need to create a hierarchy scheme. So I created a column to add the parent account IDs, but I do not know how to update dynamically.

I updated some records manually to find out how it would look, how could I make a script to update the other almost 2,000 lines?


texts: Recover Oracle Database until cancel and noresetlogs

texts:
I have to recover an Oracle database from a backup with a controlfile. I want to avoid that archivelogs written after the backup are taken for a recovery. I want to a avoid resetlogs.

I have to switch from rman to sqlplus to issue this statement.

RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE

Is there a way to do this from rman and to avoid resetlogs?


texts: How many cluster must i created on MongoDB Atlas?

texts: I want to implement MongoDB transactional. But there is a term that must not single alone server. So, how many clusters must I create to an implement that?


texts: how to grant permissions to all the synonyms inside a view?

texts:
I have to grant permissions to a view.  dbo.my_view, however, this view joins many tables, and other views and synonyms.

using the following script I can find all all the synonyms in my server:

Script to list synonym contents

SELECT
name as synonymName,
base_object_name as synonymDefinition,
COALESCE(PARSENAME(base_object_name,4),@@SERVERNAME) AS serverName,
COALESCE(PARSENAME(base_object_name,3),DB_NAME(DB_ID())) AS dbName,
COALESCE(PARSENAME(base_object_name,2),SCHEMA_NAME(SCHEMA_ID())) AS schemaName,
PARSENAME(base_object_name,1) AS objectName
FROM sys.synonyms
ORDER BY serverName,dbName,schemaName,objectName

If I have to grant select on dbo.myview how can I find out what select permissions I need to grant in any other databases that because of the synonyms also need to be granted?


texts: Oracle database startup fails several times. Giving ksvcreate

texts: Last night my production server database (ORACLE 10.2.04) was showing me number of connection exceeded as some times it show me. Then as usual I shut down abort my database then at the time database start-up it just stays blank and writing no error log and alert log after 30 min I abort the shut down by CTRL+c and issue the start up process once again then in alert.log file I found the ksvcreate: Process() creation failed this error but my database failed to start up please help me out why it is happening and any solution for it or nay parameter check for it.


texts:
I have only 2% free on tablespace UNDOTBS1.
It consist from 2 datafiles.

When I launch

alter database datafile '+DATADF/dbname/datafile/undotbs1.555.23454732' resize 32G;

I got :

  ORA-01144: File size (4194304 blocks) exceeds maximum of 4194303 blocks

How can I extend this tablespace?


texts: Does VACUUM FULL ANALYZE a partition locks the main table in postgresql?

texts:
I'm using postgresql 9.5 and partman for partitioning tables.
there is this table that has 30 partitions and volume of one of them are too higher than others. so I need to full vacuum this partition but I don't  want to lock the main table.
is it possible?


texts: How to override SSIS 2008 package config file path?

texts:
I have an SSIS 2008 package with a single configuration named Config. I would like to use the dtexec /SET switch to change the path of the config file that it uses so our developers that build the package can have it point to their local path, but when we deploy it to our production servers we can specify a new path for dtexec to load the config from so it can contain prod settings.

According to MSDN we should be able to do this using the /SET command as it mentions here:

  Using the /Set option to change the location from which the utility
  loads design-time configurations
  Behavior in SQL Server Integration Services 2008

  Succeeds.

  After the utility applies the run-time options, the utility reloads the design-time configurations from the new location.

I've tried:

dtexec /FILE "PackagePath" /SET  "\Package.Configurations[Config].Properties[ConfigurationString]";"ConfigFilePath"

and it fails with:

  The package path referenced an object that cannot be found: "\Package.Configurations[Config].Properties[ConfigurationString]". This occurs when an attempt is made to resolve a package path to an object that cannot be found.

I've also pasted a redacted version of my .dtsx file here in case anyone thinks I'm just mistyping something. And here is the contents of config that I'm trying to change it to.

I realize there are other ways of doing indirect configs using environment variables, etc... but that is not what I'm interested in so please do not offer up those suggestions. I'm specifically trying to use the /SET command since Microsoft has explicitly stated that we should be able to. Due to the way our environment is setup this method would be the best for us.


texts: Is this the correct way to model a relationship between an object which may or may not have another object?

texts:
I'm creating a database for a department store and the database has two tables: Items and Coupons. An item, like a TV, can only ever have one coupon. Some items don't have a coupon at a given time. Therefore my schema is:

Items: item_id, name, price, desc, ...
Coupons: coupon_id, item_id, rate, ...

This way, when I query for items, I get a list of all items. If I want only items with a coupon, I can either select all coupons and then individually query for the item to which a coupon belongs, or I can perform a natural join between the two tables and get a list of only items with coupons.

I don't think adding a coupon_id column to the Items table is correct because then there will be null coupon_ids, which I believe is not optimal.

Is everything I've said above correct? Or would adding the coupon_id column to Items and having null coupon_id be completely acceptable?


texts:
In the whitepaper mentioned below, Under 'Limitations' it says ASM and fan based notifications are not supported by IPv6 in Oracle. I'm just curious what ASM has to do with ipv4 or ipv6 or ipv4? ASM is just a file system.
http://www.oracle.com/technetwork/database/enterprise-edition/oracledatabaseipv6sod-2141330.pdf


texts: Updating log table in an effective manner

texts:
There is a table with compound key:
t_count

+ pageType VARCHAR(32) ascii_bin
+ pageId VARCHAR(255) utf8_bin
+ count INT

PRIMARY(pageType, pageId)

Its purpose is to track how many times each page uniquely defined by ((pageType, pageId) has been viewed. So a query should either insert a new row or increment count on the existing one. How to do it in an effective manner in MySQL?
The table will be updated from multiple concurrent threads serving pages for different clients. So the effectiveness is measured in low to no locking and the ability of the query design to operate in highly concurrent environment.
Database version is:

10.1.48-MariaDB-0+deb9u2


texts: Production SQL Server High CPU for a Single Query

texts:
I have a single query which is consuming a larger amount of CPU than I would expect in SQL 2008.  I am not able to determine why the CPU is spiking for this single query.

I can trigger an application to make a query and watch the CPU spike, typically between 40-80%, which is much higher than any other similar queries executing at the same time or when I execute this same query by hand.  In the Activity Monitor I see the spike occur and I see this single query pop up to the top of the Recent Expensive Queries list.  It will show only a handful of executions/min but will show several hundred for its CPU ms/sec (250-500 on average).  The logical reads is somewhere around several thousand.  The duration is typically 600 or so.  I have ran SQL Profiler and examined the logs to find the query being executed.  However, I'm not able to replicate the issue calling this query myself.  I can execute the query just as SQL Profile shows it, or execute it manually, and the CPU will not spike and the query never shows as a Recent Expensive Query.  It only spikes when the consuming applications makes the call.

The query is a stored procedure that joins a few large select statements together.  When I run this stored procedure manually I see very little cost in terms of CPU and I get results in less than a second. Examining the Profiler logs this stored procedure appears to be taking roughly 4 seconds to execute.

I have gone as far as to fire up duplicate images of the database and the application server.  However, making these requests from this duplicate environment doesn't replicate the issue, nor does testing in our staging, dev or local environments.

Has SQL improperly cached the query execution or something to this effect?  I recently added a couple indexes to the tables involved in this query and the execution plan I see from the Activity Monitor shows that the execution plan was updated for this change (no longer recommends these indexes).  This sounds dumb, but do I need to restart the SQL Server or something?  Or is there a way to better replicate the query that is coming into the server than pulling the query from the Profile trace?


texts: SQL Failover Clustering with Availability Groups: Single or Multiple Databases

texts:
Company wants to implement AlwaysOn SQL Failover Clustering with Availability Groups, for server level and data protection.

Are arrangements below, only possible if 1 database is on the server, or could I have multiple databases below in arrangement?

Note: First picture has 4 servers,

Second picture only has 3 failover servers. Both have availability groups.


texts: Dump with special characters in table column name

texts:
I'm taking over an old database, and while doing recovery tests from my dump I found out a table is wrongly backed up, the table definition is truncated.

Looking at the live db I've found a column name with special character in the name. That is the column name is Località.

My current mysql dump produces the following:

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `contabilita_banche` (
  `ID_Banca` int(6) unsigned NOT NULL default '0',
  `Descrizione` varchar(70) default NULL,
  `Località;
/*!40101 SET character_set_client = @saved_cs_client */;

The first problem is that the table doesn't end there, there are other fields after Località, so the structure dump is incomplete. Other than this the dump doesn't throw any error and the data is dumped correctly to the end.

I tried several commands, all without success:

mysqldump -r /tmp/cbnew2.sql --default-character-set=latin1 --no-tablespaces --skip-lock-tables --quick -u root -p -h host Dbname contabilita_banche
mysqldump -r /tmp/cbnew2.sql --default-character-set=utf8 --no-tablespaces --skip-lock-tables --quick -u root -p -h host Dbname contabilita_banche

Server version is 4.1.22-log, client 5.5.38. Is there any way to correctly produce the dump?


texts:
I would like to set a date dynamically in the where clause of my query but I can't figure out how to do it.

The query is doing a count of the number of rows with an entered on date >= 1st August year X.

The dynamic part is how the year is calculated.

If the query is run before 1st January the entered on date in the where clause will be currentyear/08/01.

If the query is run from 1st January to 31st July the entered on date in the where clause will be previousyear/08/01.

Does anyone know how this can be done?


texts: Does Express store any statistics to indicate when performance is held up due to being Express?

texts:
I am looking for business reasons to upgrade from Express to Standard. as per my question here

Does SQL Server store internally any information about when it is held up due to edition / licensing features such as number of available cores?

What queries could I run to find this out?


texts: How to connect MS sql database and php using xamp

texts:
I am trying to connect MSsql database and php using xamp. But i am unable to connect. bellow is my code. Please help me to find this.

<?php
$myServer = "x.x.x.x";
$myUser = "xxx";
$myPass = "xxxx";
$myDB = "xxxx";

//connection to the database
$dbhandle = mssql_connect($myServer, $myUser, $myPass)
  or die("Couldn't connect to SQL Server on $myServer");

//select a database to work with
$selected = mssql_select_db($myDB, $dbhandle)
  or die("Couldn't open database $myDB");

//declare the SQL statement that will query the database
$query = "SELECT id, name, year ";
$query .= "FROM cars ";
$query .= "WHERE name='BMW'";

//execute the SQL query and return records
$result = mssql_query($query);

$numRows = mssql_num_rows($result);
echo "<h1>" . $numRows . " Row" . ($numRows == 1 ? "" : "s") . " Returned     </h1>";

//display the results
while($row = mssql_fetch_array($result))
{
  echo "<li>" . $row["id"] . $row["name"] . $row["year"] . "</li>";
}
//close the connection
mssql_close($dbhandle);

Output error msg is:

Fatal error: Call to undefined function mssql_connect() in C:\xampp\htdocs\roshid\mssqlphp.php on line 8


texts: Where to store versioned/historized records?

texts:
In our application we are using versioning records using triggers. The application is supposed to handle millions of records in the future and the history/versioning tables are there to ensure that there is an audit trail for various transactions.

Currently everything is stored into one database. My concern is that these tables will bloat the transactional database, and I really want to move all record history into another database mainly because:

transactional database can be backed up more frequently since it is smaller.
indices can be scheduled to be rebuilt more frequently on the transactional db.
The history can be archived more easily if needed.

Are there any performance advantages to choosing this strategy or is the extra database another moving part which is not needed? What is the prescribed solution for this sort of thing?


texts:
I am porting a set of T-SQL stored procedures to C#/CLR. In the T-SQL, we use XACT_STATE() to determine if an error requires rolling back to a savepoint or rolling back the transaction entirely.

I cannot find an equivalent check of the transaction using the System.Data.SqlClient.SqlTransaction or System.Transactions.TransactionScope objects.

Does anyone know how to check the transaction state from within C#/CLR?


texts: Select everything besides the Max(date) for each group

texts:
I have a table where I would like to pull everything besides the max dates for each groupID.
Is there an easy way to do it?
here is what i am trying to do:
SELECT
groupId,
Types,
MAX(Dates) as date

FROM TableData

Where Dates < Max(dates)

GROUP BY
groupId,
Types

//The table looks as follows:
ID      |   GroupID | Date
1       |     A     | 10-10-2020 -> don't show
2       |     A     | 09-10-2020
3       |     A     | 08-10-2020
4       |     B     | 10-10-2020 -> don't show
5       |     B     | 09-10-2020
6       |     B     | 08-10-2020

//Expected result:
GroupID | Date
  A     | 09-10-2020
  A     | 08-10-2020
  B     | 09-10-2020
  B     | 08-10-2020


texts:
One of our CRM applications has a PostgreSQL database. Their db guys created 10 views that queries this production PostgreSQL database

We plan to do a SELECT query via a PHP script on each of these views at 06:15 every morning and it takes around 1 hour to execute. Part of this process is also to save the query result into a CSV
06:15 is pretty close to when the users actually start using this CRM application and pretty sure these queries on the views will at times overlap on CRM application usage by users
Does SELECT queries on views on PostgreSQL databases lock the tables and in turn then cause any issues on the application?
And also the other way around if a table is locked due to application usage will this cause any issues in terms of querying the view? Or will it just skip that row?
I suppose it would also be best practice to monitor server load during this time? simply use TOP command on linux instance?
Any other concerns?


texts: Understanding the given correlated subquery

texts:
I am unable to understand how this correlated subquery works on the relation Student={Sid,Sname,marks}

Select Sname from Student s1
where (select count (*) from Student s2 where s1.marks<=s2.marks)=3

I cannot understand the way in which I should comprehend this query starting from inner query. The count (*) returns the total count of rows in a group but here is it calculating for individual condition satisfied.

Can anyone help in this regard?


texts: Does MERGE prevent deadlocks and server blocking?

texts:
I have started looking at MERGE as an option that I would use in my application to process UPSERT transactions. Seems that a lot of SQL Server experts are recommending this approach. However, while exploring this method I found some common problems that MERGE can cause, for example in
Use Caution with SQL Server's MERGE Statement by Aaron Bertrand.
My system is state wide and I use ColdFusion on the back end. It is multi-thread and we expect a huge volume of requests to our database. Each of these INSERT/UPDATE transactions will handle a single row transaction. That means the user is inserting or updating one row at the time. I use transaction with rollback in ColdFusion when I call stored procedure. Is that enough or would be better to have that in SQL instead?
Here is an example of my MERGE statement used in a stored procedure:
CREATE PROCEDURE [dbo].[SaveMaster]
   @RecordID INT = NULL, -- Auto increment ID
   @Status BIT = NULL,
   @Name VARCHAR(50) = NULL,
   @Code CHAR(2) = NULL, --Primary Key
   @ActionDt DATETIME = NULL,
   @ActionID UNIQUEIDENTIFIER = NULL
AS
   MERGE dbo.Master WITH (HOLDLOCK) AS Target
   USING (SELECT @RecordID,@Status,@Name,@Code,@ActionDt,@ActionID)
   AS Source (RecordID,Status,Name,Code,ActionDt,ActionID)
      ON Target.RecID = Source.RecordID
   WHEN MATCHED THEN
      UPDATE
    SET Target.Status = Source.Status,
        Target.Name = Source.Name,
        Target.Code = Source.Code,
        Target.ActionDt = Source.ActionDt,
        Target.ActionID = Source.ActionID
   WHEN NOT MATCHED THEN
    INSERT(
        Status,Name,Code,ActionDt,ActionID
    )VALUES(
        Source.Status,
        Source.Name,
        Source.Code,
        Source.ActionDt,
        Source.ActionID
    )
OUTPUT inserted.RecID,$action as Action;

You will see that I use WITH(HOLDLOCK) to prevent primary key violations and deadlocks. After I read the article that is attached in my post, it seems that even having HOLDLOCK still you can end up with deadlocks. I'm not sure if that applies to my code above as well.
I use a @RecordID parameter to check if the row exists, and based on that MERGE will execute an insert or update. This column is an auto-incremented id that I pass back and forth in order to make sure I always update the correct row in the table. This approach is used in more than one transaction in my system.
I'm wondering if this is good enough or I should consider using Primary Key (Code is PK) instead? Or maybe have both RecordID and Code column included? Would that help in performance at all?
I'm exploring the options and the most important trying to prevent deadlocks and server blocking that seems like a big issue with these transactions in SQL world. If anyone have opinion or recommendation for my code please let me know.
Additional considerations
I have received some feedback that insert and update should be separate, and I should try to avoid MERGE for various reasons. Here is another approach that I found. I'm wondering if any of these two would be a good solution and if there is any potential problem with primary key violation or deadlocks:
begin tran
if exists (select * from table with (updlock,serializable) where key = @key)
begin
   update table set ...
   where key = @key
end
else
begin
   insert into table (key, ...)
   values (@key, ...)
end
commit tran
Or

2)

begin tran
   update table with (serializable) set ...
   where key = @key

   if @@rowcount = 0
   begin
      insert into table (key, ...) values (@key,..)
   end
commit tran

I know that my SQL Server 2008 needs an upgrade but that is something that I do not control unfortunately.


texts: Set up a SQL Server database based on an Excel file which refreshes automatically

texts:
Can I set up a Server database based on an Excel file, which refreshes automatically with any changes in the source Excel file?

I am currently using SQL Server 2008.


texts: What is the difference between right/left placement of column name in a WHERE clause?

texts:
Both of the following queries return the same output but I'm curious whether there is underlying difference between these two syntaxes.

    SELECT * FROM table
    WHERE first_name = 'John';

and

    SELECT * FROM table
    WHERE 'John' = first_name;


texts: How to copy wt files from another mongo instance?

texts: I received a copy of mongo dbpath directory copy from a peer. I copied entire content to my local dbpath with an intention to work on the collections. However, my local is not recognizing the databases or the collections. Am I missing anything?


texts: Store table in separate filegroup in SQL Server

texts:
I have big SQL Server database, with a lot of blob data in some tables.

For performance optimization, I want to move one table to separate hard drive.

I created new filegroup, and "row data" file.

But, I can't change any parameters in table properties: they are all disabled.

And I don't know a way to enable it.

Can anybody help me?


texts: How pt-online-schema-change ensures consistency during data copy

texts:
I have read pt-online-schema-change documentation and understood that it works by creating triggers and copying data in chunks with in chunk-time seconds(0.5 seconds in default)

Let us consider this below case.

    We have a table TBL1 of 5 GB and is being altered with PT-ONLINE-SCHEMA-CHANGE

  PT-ONLINE-SCHEMA-CHANGE created new table with the desired change, added trigggers on
     TBL1 and started copying.

    copied 10 chunks of data and started copying 11th chunk which is some 'X' MB in size.
       As per tool, this chunk be copied in a max of 500 milliseconds or 0.5 seconds

  During the 11th chunk copying, application issued an UPDATE which has to modify effect
     rows that are part of this 11th chunk of data.

My question is that which of the below things happen now.original table now ?

A) Update will be blocked till the chunk be copied and then applied on original table
   which gets updated through triggers on new one.

B) Update will be performed on original table and data chunk will be re copied all
   over again.

If Option A is the answer, how PT-ONLINE-SCHEMA-CHANGE can be lock free ?

If Option B is the answer, how it knows the chunk being copied is modified ?

Thanks in advance.


texts:
I have a large table which contains sensor data, along with the fields:

sensor_id
timestamp

I do queries against it using these fields almost exclusively. There are multiple sensors, and different sensors might have the same timestamp for a given set of data, so neither index can be unique.

I created three indexes: one for each of these columns (not unique), and a compound one for both (unique).

The table is constantly being written to, and queries to read data seem increasingly slow.

My question is, is the compound index unnecessary? Would it be faster to have only the two separate indexes? (Or remove those and keep only the compound index?) No other columns are used for filtering query data.

My question is similar to this one: Do I need separate indexes for each type of query, or will one multi-column index work?

Edit:

Here's the table definition:

CREATE TABLE `data` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sensor_id` int(10) unsigned NOT NULL,
  `timestamp` datetime NOT NULL,
  -- 71 other columns...
  PRIMARY KEY (`id`),
  UNIQUE KEY `IX_sensor_timestamp` (`sensor_id`,`timestamp`),
  KEY `IX_timestamp` (`timestamp`),
  KEY `IX_sensor_id` (`sensor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=78542614 DEFAULT CHARSET=latin1


texts: Correct way to perform an UPDATE on multiple tables?

texts:
This query is working, but I am only just beginning to understand relational tables. I have spent the last few yeas doing all of the processing with PHP after retrieving the data I needed (oftentimes using multiple queries). So, please hold my hand here, and tell me if this is being done the "right" way?:

Table Breakdowns

SettingsList

UserSettings

SettingTypes

Basically when I add a setting to the API (through the control panel) it gets put into SettingsList, I give the setting a value and a type (so that it actually does something) in the UserSettings table (this will be the only table accessed by the client from the app) . and the setting itself can only be a type specified in the enumeration (of sorts) in the SettingTypes table.

Right now, when I edit a setting from MY administrative control panel (the client will have far less control), I am running an update qiery that looks something like this:

UPDATE `SettingsList` l, `UserSettings` u
SET
l.settingName = 'pow',
l.settingDescription = 'otherTest',
u.setting = 0,
u.type = 1 WHERE
l.settingID = 3 AND u.settingID = 3

Does this look correct? Again, it does work but as most here would agree, just because it works, doesn't mean I did it right.

Well, I may have supplied more information than you need to give me an answer, but how am I doing in my first application with relational tables?


texts: Creating the same unique ID in multiple tables

texts:
I have two tables of data; one table is the Employee Master table containing a list of unique employee names, and the other table contains transactional data with multiple records for each employee.

There is no unique number that ties the two tables together. I would like to assign the same unique ID to the employee names within each table.

For the Employee Master table, I was able to do this with this code:

select employee_name, row_number() Over(order by employee_name asc) as employee_id
from HREmployeeMaster
group by employee_name

However, no matter what I try, for the transaction table, I cannot assign one ID to the distinct employee names. Instead, I keep getting an ID for each record in the table.

Any advice would be appreciated.


texts: Get WAL files from AWS RDS PostgreSQL instance

texts:
We have a Postgres RDS instance on Amazon Web Services.  We have automatic backups enabled, and we take snapshots on a daily basis.  We would like to generate a local 'up-to-date' backup of the RDS instance that we can manage ourselves.  Running pg_dump against the instance is not sufficient because we want to be able to restore the database to any point in time.  We would prefer to have a local backup of RDS and all WAL files since that backup was taken.  Questions:

Is it possible to access the WAL files and backups that RDS is automatically generating in its backup routine?  This would be ideal.  I would want to download a local copy of them.  After initial investigation, I feel the answer to this question is 'no'.  It sounds like RDS is storing its WAL files and backups in S3, but it makes them inaccessible to us.  I would love confirmation.
Is there any other way to access transactions (WAL files) that have occurred on the RDS instance?  I imagine we should be able to create a Postgres database on an EC2 and 'feed' transactions from our primary 'live' RDS instance into this EC2 instance.  Once our EC2 instance is updated, we could pull WAL files from there.  What a headache, though :/  Is this setup possible?  What is the magic to 'feed' from our RDS instance to the EC2 instance so that it is always up to date?

Thanks!


texts: Using REGEX_LIKE to Implement a Check Constraint

texts:
Background

I am working on the database design for an application that tracks information about database development projects at my company.  The project management office (PMO) assigns a psued-number to each project based on if the project's cost is counted toward merger integration or not.  To illustrate, the number assigned will either start with INT (for a project contributing to merger integration) or NINT (for a project not contributing to merger integration).  Then a sequential number is assigned.  For example, INT175 would be 175'th project identified contributing to merger integration.  An additional wrinkle is the PMO sometimes wants to create multiple projects with the same number as they are "related".  So for example there could be an INT175a project, an INT175b project, and so on.

While the PMO considers this number to be the identifier for a project, in the database I will only use the column as an alternate unique key both to ensure duplicate projects are not created and as a search key for users.  I want to implement a check constraint on that column to ensure only a valid number matching the rules I gave above can be entered.

What I've Tried - Regular Expressions

My first thought was to use a regular expression.  In Oracle, my target DBMS, we have the REGEX_LIKE function that can be used.  I researched the documentation (here, here, here, here, and here )  on regular expressions and found the options to be dizzying!  For whatever reason my brain gets tangled up trying to take the list of all the various pattern matching options and apply it to my specific example.  I'm one of those people who work best with examples.  Unfortunately, I haven't found many examples out there.  What I have found have either been too simple, or so complex I couldn't grasp them.

What I have tried thus far is something like this:

select c
from (
  select 'INT1756b' c from dual union all
  select 'INT175a' c from dual union all
  select 'INT75a' c from dual union all
  select 'INT75' c from dual union all
  select 'NINT2283a' c from dual union all
  select 'NINT2283' c from dual union all
  select 'NINT915c' c from dual union all
  select 'NINT915' c from dual union all
  select 'NINT95b' c from dual union all
  select 'NINT95' c from dual union all
  select 'ABC123' c from dual
) x
where REGEXP_LIKE ( c, '(NINT|INT)\d{2}[0-9]|[a-z]' );

My test "table" x has what I foresee as an example of all the possible combinations of valid numbers plus one invalid number.  They will all either start with INT or NINT, and then have at least 2 numbers up to 4 numbers, and then optionally a lower case letter on the end.

My Struggle - Handling Varying Lengths

What I am really struggling with is how to deal with the varying lengths of the numbers.  There can be 2, or 3, or 4 numbers, which may or may not then be followed by a lower case letter.  What I came up with above, just as a starter, was to look for 2 numbers after the INT or NINT.  I know there will always be at least two numbers.  Then I just look to see if the rest are in the set of digits or lower case letters.  Now I know this isn't good enough but it is all I could come up with after a day or two of racking my brain on this.

Plea for Help!

Has anyone done something similar with regular expressions who could point me in the right direction or give me an example?  Is there a more elegant and simple way to implement this constraint?  I appreciate any and all help and pointers!


texts: Does CAST(DateTime AS DATE) cause the entire table to be CAST before comparison?

texts:
This is a very common need in the WHERE clause of our SQL statements:

 CAST(Something.CreationDate AS DATE) BETWEEN CAST(@firstDate AS DATE)
 AND CAST(@lastDate AS DATE)

All of the fields are of type DateTime that are cast to DATE. Does this cause a conversion to run across the entire table first and then comparisons happens or is this very similar to a scan on that table?


texts: How can I change Referential Integrity from a Unique Key Constraint to a Primary Key?

texts:
(without dropping foreign keys)

I have migrated a SQL Server CE database to SQL Server 2012, however, referential integrity was enforced via unique key constraints on the surrogate INT identity columns.

e.g.

create table Table1
(
   Table1ID INT NOT NULL,
   CONSTRAINT U_000001 UNIQUE(Table1ID)
);

CREATE TABLE Table2
(
   Table2ID INT NOT NULL,
   Table1ID INT NOT NULL,
   CONSTRAINT FK_T2_T1 FOREIGN KEY(Table1ID) REFERENCES Table1(Table1ID)
);

I would like to convert the unique key constraints to primary keys, for ERD diagramming and ORM reasons.

I have added the primary keys, but cannot drop the UKC, because RI of dependent columns is already 'baked into' the UKC despite the alternative PK now being available.

ALTER TABLE Table1 ADD CONSTRAINT PK_Table1 PRIMARY KEY (Table1ID);

ALTER TABLE Table1 DROP CONSTRAINT U_000001;

Fails with

  Msg 3725, Level 16, State 0, Line 1
  The constraint 'U_000001' is being referenced by table 'Table2', foreign key constraint 'FK_T2_T1'.

So my question is, is there any way to change the RI checks on foreign keys to use the PRIMARY KEY, and not the UKC as is currently the case, without having to drop the foreign keys on referencing tables first?

I have prepared a small SQL Fiddle here.


texts: Reindexing / rebuild Indexes on Linked Server tables

texts:
I am trying to run the below query to rebuild the index on a table on the Linked server, but it is not working. Can any one please let me know what I am doing wrong?

ALTER INDEX ALL ON [LinkedServerName].[dbname].[dbo].[tablename] REBUILD
WITH (FILLFACTOR = 90)

This is giving me below error:

  Cannot find the object "[LinkedServerName].[dbname].[dbo].[tablename]" because it does not exist or you do not have permissions.


texts: Can I remove some of our disk allocation?

texts:
I've started work for a new company who are using Azure for their SQL Databases.

As one of my first tasks, they've asked me if they have too much storage (expensive storage), and would like to remove (and save money) by cancelling some of their storage (disk #2 in the attached screenshot).

I have no idea if this is possible or how to do this ?

Screenshot of the disks is attached...

Any help possible here please ?


texts: How to get the timestamp column in only milliseconds from PostgreSQL?

texts:
I have a column "created" with type timestamp without time zone default now() in a PostgreSQL database.

If I select colums, it has a nice and readable format per default:

SELECT created FROM mytable;

         created
---------------------------
2011-05-17 10:40:28.876944

But I would like to get the timestamp in only milliseconds (as a Long). Something like this:

SELECT myformat(created) FROM mytable;

     created
-----------------
2432432343876944

How can I get the timestamp column in only milliseconds from PostgreSQL?

Response to Jack:

I do get the same difference as you (-3600), but if I use timestamp with time zone I can see that the "error" or difference is because '1970-01-01' gets time zone +01.

create table my_table_2(created timestamp with time zone);
CREATE TABLE
insert into my_table_2 (created) values (now()), ('1970-01-01');
INSERT 0 2
select created, extract(epoch from created) from my_table_2;
            created            |    date_part
-------------------------------+------------------
 2011-05-18 11:03:16.909338+02 | 1305709396.90934
 1970-01-01 00:00:00+01        |            -3600
(2 rows)

Is the difference a bug? I may be because of "Daylight saving times" at the moment?

Also interesting while using to_timestamp() to insert timestamp 0 and 1.

insert into my_table_2 (created) values (to_timestamp(0));
INSERT 0 1

insert into my_table_2 (created) values (to_timestamp(1));
INSERT 0 1
select created, extract(epoch from created) from my_table_2;
            created            |    date_part
-------------------------------+------------------
 2011-05-18 11:03:16.909338+02 | 1305709396.90934
 1970-01-01 00:00:00+01        |            -3600
 1970-01-01 01:00:00+01        |                0
 1970-01-01 01:00:01+01        |                1


texts: Recommendations to prevent failover (scheduled)

texts:
I have a two node SQL Server failover cluster with availability group. This is used for SharePoint.
SharePoint rebuilds the indexes where necessary every night. This routinely causes failover.
I can't get this resolved with the infrastructure team, so I want to mitigate.
Since the index rebuild is scheduled, I am considering:

Suspend secondary node for duration (1 hour)
Change failover from automatic to manual for duration (1hour)

Anything else? Which is best? I know it's not ideal and the root cause should be fixed but I am out of options.

Thanks
Andrew


texts: Unix domain socket "/var/run/postgresql/.s.PGSQL.5432" missing

texts:
I try to run an application where its database on postgresql. It won't work and I'm getting the following error whenever I try to interact with the Postgres service:

could not connect to server: Aucun fichier ou dossier de ce type
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432`

I checked if Postgresql service is running

sudo systemctl status postgresql

postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enab
   Active: active (exited) since Thu 2019-04-04 10:28:27 +01; 58min ago
 Main PID: 1212 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 3522)
   CGroup: /system.slice/postgresql.service

avril 04 10:28:27 ubuntu systemd[1]: Starting PostgreSQL RDBMS...
avril 04 10:28:27 ubuntu systemd[1]: Started PostgreSQL RDBMS.

checked the content of the socket unix connection

ls /var/run/postgresql/

got this empty folder

brahim@ubuntu:/var/log/postgresql$ ls /var/run/postgresql/
10-main.pg_stat_tmp

checked the content of the data directory

ls /var/lib/postgresql/10/main/

it contains the following

 base         pg_logical    pg_serial     pg_subtrans  pg_wal
 global       pg_multixact  pg_snapshots  pg_tblspc    pg_xact
 pg_commit_ts  pg_notify     pg_stat      pg_twophase
 postgresql.auto.conf pg_dynshmem   pg_replslot   pg_stat_tmp
 PG_VERSION   postmaster.opts

the file postgresql.conf has the following configuration

  # - Connection Settings -
  #listen_addresses = 'localhost'
  port = 5432
  max_connections = 100
  unix_socket_directories = '/var/run/postgresql'

I tried to uncomment the listen_addresses instruction and it didn't work, also I've tried with '*' for all, same thing

the postgresql file log has

2019-04-04 12:17:57.765 +01 [942] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-04-04 12:17:57.767 +01 [942] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2019-04-04 12:17:58.190 +01 [971] LOG:  database system was shut down at 2019-04-03 20:19:54 +01
2019-04-04 12:17:58.192 +01 [971] PANIC:  replication checkpoint has wrong magic 1886413116 instead of 307747550
2019-04-04 12:17:59.900 +01 [942] LOG:  startup process (PID 971) was terminated by signal 6: Aborted
2019-04-04 12:17:59.900 +01 [942] LOG:  aborting startup due to startup process failure
2019-04-04 12:17:59.902 +01 [942] LOG:  database system is shut down

I am running postgresql 10.6
any thoughts


texts: Intermittent only_full_group_by errors in AWS Aurora MySQL cluster db

texts:
We recently upgraded from MySQL 5.6 to 5.7 and are running into errors related to only_full_group_by sql mode. We are only seeing the problem intermittently. Our database lives in an AWS Aurora MySQL cluster which has a single instance. We originally included the only_full_group_by sql mode in the cluster parameter group setting, but began running into these errors and decided the number of queries which required updating, are outside the scope of this project.
Looking for some insight into why the errors are intermittent and the best course of action to resolve the errors completely.


texts: postgres 9.1: both md5 and ident on the same connection type?

texts:
In pg_hba.conf, is there a way to enable both 'ident' and 'md5' for local connections?

Concretely, I am happy to have 'ident' turned on for humans to interact with the database. However, if I set up an httpd instance (e.g.), I don't like having to add a full linux user so that it can connect to the db via ident. I'd like to just assign a password inside postgres.

My experiments in having two lines in pg_hba.conf for 'local' didn't work, but the doc is a bit ambiguous, and I thought perhaps that I'd missed something.


texts: How would you store permutations of a set of simulation parameters, optimizing for storage and efficiency of parsing?

texts:
I'm going to run a bunch of simulations using an application I wrote in the near future, and I'm having trouble designing my database around this.

Here's how it works. Each simulation has 6 parameters that can vary, and each parameter has ~10 options. So we have about 1 million permutations of this to somehow store in the DB. The simplest way I can think is to have a column for each parameter, with an additional column for my primary key (ExperimentID). I could then access the parameters for a given experiment directly by doing a simple

SELECT * FROM Parameters WHERE ExperimentID = (givenID)

Each parameter will be stored in the DB as an int, which will be interpreted by my application as the necessary parameters at runtime.

A more complex, but I think faster, way to do it is to take advantage of the speed of binary shifting. Instead of storing the parameters directly, I'd store the information about each parameter in 4 bits of an unsigned int. I don't actually know how ints are stored (binary wise) in the DB, so that's the major hold up.

Any advice on what to do in this situation? Also, I don't yet have these columns, so if I decided to go with the first option, how would I fill a table with all of the permutations of a set of parameters?


texts: How to replicate MySql database between two 2012 server

texts:
I am setting up a disaster recovery strategy for two 2012 servers DB1 and DB2 in one location and may be later I plan to set them in different locations, I want DB2 to take control incase of of DB1 failure.
They are installed with MySql 5.5 database application and for now DB2 has no data.

How can can I replicate and synch these two servers? and create backups from DB2?


texts: Would I lose data if I change a column from varbinary(8000) to varbinary(max)?

texts: Is there any data loss if a table is being altered to change an existing column from varbinary(8000) to varbinary(MAX)?


texts: Changing Compatibility level from 90 to 120 in sql 2014 migration

texts:
I have SQL 2008R2  server to migrate to SQL2014 Server.

A side by side upgrade is what we are thinking for few databases in SQL 2008R2 which are having compatibility level of 90 or 80.

Can I migrate these databases (back& restore) to SQL2014 and sql will allow me to change compatibility level to 120 ? if not how can i deal with this?

thanks


texts: SQL Server 2008 mirroring migration to SQL Server 2012 always on

texts:
Quick question, request your input.
I am migrating a SQL Server 2008 R2 instance with database mirroring to SQL Server 2012 with always on.
Should I :

Restore the SQL Server 2008 R2 backup to SQL Server 2012 primary and mirror (with no recovery), setup the mirroring and then do the post upgrade steps like dbcc checkdb, update stats etc only on primary
OR

Restore the SQL Server 2008 R2 backup to SQL Server 2012 primary. Complete the post upgrade steps like dbcc checkdb, update stats etc on primary. Then take fresh backup of this instance and setup the SQL Server 2012 always on on secondary.

Thank you in advance for your kind recommendations


texts: SYSDBA privileges of viewing users table

texts: Is there anyway which would restrict sysdba from watching the users tables. It sounds ridiculous but we sort of need this functionality. We only want sysdba to act as administrator only, no peeping into user's tables. Is it possible?


texts: What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?

texts: What are the drawbacks of using Galera Cluster instead of regular Master/Slave Replication? Galera's 0 slave lag time, Synchronous replication and no Single point of failure seem very appealing then why isn't Galera cluster as common?


texts: Which performance counters can you look at on a SQL Server Instance to determine it's performance and over all health?

texts:
I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the experts in the field.

One of my questions is:

Which performance counters can you look at on a SQL Server Instance to determine it's performance and overall health?

Particularly I am interested in the threshold values when good becomes bad.

Jamil Young
Eindhoven
The Netherlands


texts: Does READ COMMITED commited blocks any rows?

texts:
I know that there are four diffrent types of isolation levels in databases. I've read many articles about that but still, I don't get the idea of Read Commited level. Does this level lock the rows which we are trying to get?

For example:

Transaction 1 - READ COMMITED

begin;

select * from table <- Is this query locking anything?

update table set id = 2 where id =3;

Is this query locking anything?

If after this query will be another query from transaction REPETABLE READ will we be able to execute select from second transation?


texts: Duplicate keys error after switching to MySQL Galera

texts:
I recently switched from a single MySQL installation to MySQL Galera, running on 3 nodes addressed via HAProxy. Ever since switching I do get randomly "duplicate key" errors reported which theoretically should not be possible as I do check if the dataset is already available.

e.g.:

MySQL Error executing stmt:

        INSERT INTO user_ips
            (user_id,ip)
        VALUES
            ('444079','41.205.7.198, 176.9.19.101')

 Error No:  1062
 Error:         Duplicate entry '444079-41.205.7.198, 1' for key 'user_id'

My code looks like this:

function save_user_ip($user_id){
    global $T62, $link;
    $ip = $_SERVER['REMOTE_ADDR'];
    // do we already know that user by this ip?
    $stmt="
        SELECT ID AS ip_id
        FROM $T62
        WHERE user_id = '$user_id' AND ip = '$ip'
    ";
    $row = db_get_row2($stmt);
    $ip_id = isset($row->ip_id) ? $row->ip_id : "";

    if (!$ip_id){ // ip unknown for that user, save it
        $stmt="
            INSERT INTO $T62
                (user_id,ip)
            VALUES
                ('$user_id','$ip')
        ";
        db_query($stmt);
        $ip_id = db_insertid($link);
    }
    return $ip_id;
}

Running this on a test page it works as intended. On the production server under load it however does not always work. I thought about INSERT IGNORE but would rather not run an insert on all actions as writes are slower and it also triggers a warning.

Running SHOW CREATE TABLE tablename creates following output:
1146 - Table 'tablename' doesn't exist

One possible solution that seems to work at least on development cluster:

// save ip. Will ignore if already known ip
$stmt="
    INSERT IGNORE INTO $T62
        (user_id,ip)
    VALUES
        ('$user_id','$ip')
";
#echo $stmt; exit;
db_query($stmt);
$ip_id = db_insertid($link);
// if ip already known, retrieve it
if (!$ip_id){
    $stmt="
        SELECT ID AS ip_id
        FROM $T62
        WHERE user_id = '$user_id' AND ip = '$ip'
    ";
    $row = db_get_row2($stmt);
    $ip_id = $row->ip_id;
}
return $ip_id;

Not sure how this improves the situation on prod or if it makes things even worse. There might be a more elegant solution.

Thank you in advance for any help on this.


texts: What do most people use to work with SSIS packages since VS2015 UI is buggy?

texts:
I don't think I can post bugs on Microsoft connect. I'm trying to use SSDT on VS community edition. And the UI is terribly buggy. Here are some screenshots:

The Enumerator properties manager is just painful. I can drag it down a tiny bit but can still barely read one line at a time.

And this image is another example. What tools do most people use to develop SSIS in? I can't believe that it's the same ones I'm using...

I'm using a surface pro 4 that is up-to-date as far as I know


texts: How do I open Data Pump Import Wizard in SQL Developer 4.1?

texts:
How do I find and open the Data Pump Import Wizard in SQL Developer 4.1?

I opened the Help search tool in SQL Developer 4.1 and searched for Data Pump, and found directions that include:

1.) Select View > DBA from the View menu.
2.) Expand a connection in DBA Navigator.
3.) Right click on Import Jobs.
4.) Select Data Pump Import Wizard.

The problem is that, when I expand a connection in what seems to be DBA Navigator (after selecting View > DBA), there is no Import Jobs option, even though there are many other options listed.  I did go to the trouble of expanding every single option including nested submenus, and there still was no Import Jobs option anywhere.

In addition to the embedded help within SQL Developer 4.1, I also googled for instructions, but the results were only for version 3.1, such as this link.

I am trying to import data into an Oracle 11g r2 database using the Data Pump Import Wizard.

So how do I open the Data Pump Import Wizard in SQL Developer 4.1?


texts: mysqldump dumps different data with and without --no-create-info

texts:
mysqldump dumps different representations of data when called with/without --no-create-info.
Test case
First, create the test table and populate it with interesting data.
CREATE TABLE `test` (
  `value` longtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- insert 'crème'
insert into test values(UNHEX('6372C3A86D65'));

Then, dump the data using with and without --no-create-info:
mysqldump test_db test > dump_test.sql
mysqldump --no-create-info test_db test > dump_test2.sql

These files encode crème differently:

dump_test.sql encodes it as 0x6372C3A86D65 (è is 0xC3A8)
dump_test2.sql encodes it as 0x63725CE86D65 (è is 0x5CE8)

Restoring the dump from dump_test.sql works correctly, a complete row is restored:
mysql test_db < dump_test.sql

Restoring the dump from dump_test2.sql produces an incorrect result where data is truncated to just cr (0x6372). Note that structure of the table is the same:
mysql test_db < dump_test2.sql

No error nor warning is given. These two files differ - besides encoding of crème - only by:
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `test` (
  `value` longtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--

Question
Why does this happen? Is it documented anywhere? Can I/how can I correctly restore the dump created with --no-create-info data?
Additional info
# mysqldump used to dump the database
$ mysqldump -V
mysqldump  Ver 8.0.22-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
# Server
version_component: Percona Server (GPL), Release '35', Revision '5688520'
version: 5.7.32-35


texts: How to insert (file) data into a PostgreSQL bytea column?

texts:
This question is not about bytea v. oid v. blobs v. large objects, etc.

I have a table containing a primary key integer field and a bytea field. I'd like to enter data into the bytea field. This can, presumably, be done by one of the PL/ languages, and I may look into doing this with PL/Python in the future.

As I am still testing and experimenting, I would simply like to insert data from a file (on the server) using "standard" SQL statements. I am aware that only administrators with write permission on the server would be able to insert data in the way I would like to. I'm not concerned about that at this stage as users would not be inserting bytea data at present. I have searched the various StackExchange sites, the PostgreSQL Archives and the Internet generally, but have not been able to find an answer.

Edit: This discussion from 2008 implies that what I want to do is not possible. How are bytea fields used then?

Edit: This similar question from 2005 remains unanswered.

Solved: The details provided here on the psycopg website provided the basis for a solution I've written in Python. It may also be possible to insert binary data into a bytea column using PL/Python. I don't know if this is possible using "pure" SQL.


texts: Understanding Oracle "LOG_ARCHIVE_START" parameter

texts:
I've enabled ARCHIVELOG mode on a Oracle 12c multitenant instance:

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
ARCHIVELOG

I understand that REDOLOGS are correctly archived by finding files in the configured path:

[root@LNXORA12 CSCONT01]# find ./ -type f
[...]
./archivelog/2017_03_22/o1_mf_1_5074_df3kfxmb_.arc
./archivelog/2017_03_22/o1_mf_1_5075_df417blj_.arc
./archivelog/2017_03_22/o1_mf_1_5076_df4bqqmf_.arc
./archivelog/2017_03_22/o1_mf_1_5077_df4q73mk_.arc
./archivelog/2017_03_22/o1_mf_1_5078_df4syvms_.arc
./archivelog/2017_03_22/o1_mf_1_5079_df4ttzs7_.arc
./archivelog/2017_03_22/o1_mf_1_5080_df501hrb_.arc
./archivelog/2017_03_22/o1_mf_1_5081_df57w8rj_.arc
./archivelog/2017_03_22/o1_mf_1_5082_df5gx2r5_.arc
./archivelog/2017_03_22/o1_mf_1_5083_df5mhsvj_.arc
./archivelog/2017_03_22/o1_mf_1_5084_df5sj5rx_.arc
./archivelog/2017_03_22/o1_mf_1_5085_df5sk6so_.arc
./archivelog/2017_03_22/o1_mf_1_5086_df5sq6sr_.arc
./archivelog/2017_03_22/o1_mf_1_5087_df5x4mto_.arc
./archivelog/2017_03_23/o1_mf_1_5088_df629bv9_.arc
./archivelog/2017_03_23/o1_mf_1_5089_df6ommvm_.arc
./archivelog/2017_03_23/o1_mf_1_5090_df73j2tt_.arc
[...]

Reading on many docs here and here the automatic archiving process is enabled only if LOG_ARCHIVE_START is set to TRUE but mine is set to FALSE.

SQL> select value from v$system_parameter where name = 'log_archive_start';

VALUE
--------------------------------------------------------------------------------
FALSE

Are my REDOLOGS really get archived?


texts: copy command in postgresql to append data

texts:
I am executing copy command from a function

execute 'copy (select * from tableName) to ''/tmp/result.txt'''

It works fine. But, tableName is dynamic and will be having more tables (iterating in a loop). result.txt is having only the last iteration (table) data.

I could do, if I had to, via the command line with \o or >> by appending to a file. But, I have to use the function.

I read http://shuber.io/reading-from-the-filesystem-with-postgres/.  It suggests a solution using temporary tables. Can I do something similar, but without temp tables? (I must avoid archive logs).  Will creating a temp table create archive logs?

Is there any way to do appending with copy command in a function?


texts: Merge Replication Invalid Object ( on an object that no longer exists ) ComErrorException

texts:
My merge replication scheme has suddenly started failing.  When trying to sync it complains that there is an invalid object.  'dbo.myfunction'.  The function used to exist but was removed.  There was only one reference to it from a table valued function and it has been updated as well.  I've generated a new snapshot but it continues to complain that the object is invalid and will not sync at the subscriber.

Somewhere within the replication it's still looking for that function.  How do I find and remove it - when it's not there anymore.

I've even looked through the files in the snapshot and cannot find anywhere that it is being referenced.

My publisher is SQL 2008 R2 - all my subscribers are SQL 2008 Express


texts: Upgrade DAC wizard rebuilds schemabound views for no apparent reason

texts:
We recently did a deployment that included upgrading a database via DACPAC.
We extracted the script from the SSMS Upgrade DAC wizard, removed the objectionable parts (dropping users, extended properties; that sort of thing).
We ran the script. The deployment succeeded.

After the deployment, we ran the wizard and extracted another script using the same DACPAC. I expected the only differences to be that it would try to drop the users and extended properties again.
But instead, the script decided to alter/rebuild several schemabound views, and to drop and rebuild the indexes on those views, and of course all the sprocs and functions that depend on at those views.
The schema we were deploying is 100% identical to the one we're upgrading "from", other than the users, extended properties, that sort of thing.

The views are materialized, indexed, schemabound.
OLTP is not a factor here.
SQL Server 2016, compatibility mode 2016.
DACPAC built on VS2019 16.6.3, SSDT 16.0.62006.03190
The database is not marked as a Data Tier Application in SQL Server, because deploying to those requires more access.

Why does it want to rebuild these views, when they haven't changed?


texts: PostgreSQL Master server act as a slave for 2nd DB on new server

texts:
I have a strange situation I'm are trying to resolve. I've googled around but I haven't found a clear answer so I thought I'd pose it to the good people at DBA on StackEx to get a second opinion.

We have a Master server(server1) that has a Hot Standby attached(server2). This runs great (using SR).

We've ended up acquiring a new PostgreSQL server(server3) that has it's own DB and software that regularly adds INSERTs into a table. We need to get that table's INSERTs over to our Master(server1). I am trying to figure out how best to do this using PostgreSQL only. All servers are in the same warehouse now and are on the same LAN(connection reliability/latency is excellent).

My questions are:

Is there a way to have my Master(server1) act as a slave to server3 while still being the Master for the HS(server2)?
If that is possible, will the INSERTs that are replicated to the Master(server1) from server3 likely bring with them a serious performance hit? I would be using asynchronous replication.
I'm the only one here with any DB experience so I have no one to bounce ideas off of... Am I missing anything obvious? Is there a better way to get the table receiving the INSERTs on server3 over to server1? I can't decommission server3 for various reasons.

As a note: I have looked at/considered the following other options:

SQLWorkBench - I'd like to avoid adding JRE to the servers if possible.
Batch SELECT/COPY to an output file on server3 that is dropped onto a file share owned by Master(server1) that it then picks up and works on.
Because of the steady stream of INSERTs pg_dump is not a solution.


texts: Clustered Index and Primary Key on table with unique identifier and date

texts:
I have table with Orders with unique incrementing ID and (order creation) Date. This table is quite large and wide (5mn rows, 50 columns, 10 columns of which are FK IDs). Example:

CREATE TABLE Orders As (
    ID int UNIQUE AUTOINCREMENT (1,1),
    DATE datetime,  --This is SQL Server 2005, so no [date] data type
    OrderStatus char (2),
    ClientID int
    ...
)

I get this table as copy of yesterday's production table. Table (as whole DB) is used for reporting purposes and therefore read-only.

Part 1

I have 2 very common use cases:

80% (or more) of queries have DATE column in WHERE clause, as users want data for specific business date.

Here I want to create clustered index on DATE.

40%-60% of queries use OrderID to JOIN Orders table to other tables with information about Order details (Product, Supplpier, Payments, Reservations, etc).

Here I want to create clustered index on OrderID.

Part 2

Can I have one CI for both cases?
Index (ID, DATE)  will not work with WHERE clause.
Index (DATE,ID) will not work for joins on ID only or ID in WHERE

Catch. We know that DATE is incremental as well as ID. ID with higher value cannot appear yesterday under any circumstances as it is AUTOINCREMENT.

Question. Is there a way to tell SQL Server that CI (DATE, ID) will have ID sequentially ordered for all dates?

My only solution at the moment is crating non clustered covering index (ID, DATE), but it's suboptimal.

I've searched for some time, but could not find anything. If there is a solutions for later versions of SQL Server I would be interested in it too.

Update

I know clustered index basics. Please note, database is put in read-only state for users.

Logically you can just ignore Date column in (Date, ID) index without any harm at all. Possibly this is a very specific use case that is not yet covered by SQL Server functionality.


texts: SQL Server on Linux hangs on initial startup, no errors, and no new / updated ErrorLog file

texts:
I'm using SQL Server 2017, Release Candidate 2 (RC2) on Linux (Ubuntu 16.04).
When the server starts up, SQL Server usually starts as well. But for some reason, SQL Server won't start anymore. At least I cannot connect to it using sqlcmd. I get an ODBC timeout ("Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server") error every time now:
Login timeout expired.
TCP Provider: Error code 0x2749.
A network-related or instance-specific error has occurred while establishing a
connection to SQL Server. Server is not found or not accessible. Check if instance
name is correct and if SQL Server is configured to allow remote connections.
For more information see SQL Server Books Online..

However, when I run:
ps aux | grep mssql

I get two entries returned showing that the mssql user is running the sqlservr process.
Also, the errorlog file in /var/opt/mssql/log/ does not have a timestamp matching when I started the VM (or restarted the service), nor are there any new entries in that file.
AND, in /var/log/messages, all that shows up is:

This is an evaluation version.  There are [141] days left in the evaluation period.

If I run systemctl status mssql-server, then I get the following:

● mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
Active: failed (Result: exit-code) since Mon 2017-09-04 20:01:56 BST; 36s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Process: 8009 ExecStart=/opt/mssql/bin/sqlservr (code=exited, status=255)
Main PID: 8009 (code=exited, status=255)
Started Microsoft SQL Server Database Engine.
This is an evaluation version.  There are [141] days left in the evaluation period.
Stopping Microsoft SQL Server Database Engine...
mssql-server.service: Main process exited, code=exited, status=255/n/a
Stopped Microsoft SQL Server Database Engine.
mssql-server.service: Unit entered failed state.
mssql-server.service: Failed with result 'exit-code'.


texts:
We are in the early stages of planning to move some of our on-premise database to cloud and wanted to go the PaaS way.  I understand that managed instance is in preview.

From what I have read, ignoring the cost, is there any reason why not to go with managed instance? Does it not make the migration easier while still being PaaS/Managed so that we do not have maintain it?  Looks like the best of both world to me.

The only question that I couldn't find is how we could handle disaster recovery for managed instance?  With Azure SQL DB we can have Geo-replication in another region, but couldnt find anything similar documented for managed instance.

In the initial stages we are planning to move an OLTP application which needs to be highly available and have very low latency queries.


texts: Collect CPU, reads and writes when replaying SQL Profiler Trace

texts:
I'm trying to replay a trace on a test environment with SQL Profiler in order to benchmark a database prior to making some performance tweaks. I intend to replay the same trace at various stages to compare the stats and measure the improvements.

I've been following the post at keepitsimpleandfast which seems good however I'm not getting the CPU, Reads and Writes data saved when I replay the trace in Step 6..

I had to add the CPU, Reads and writes columns to events when I set up the TSQL-Replay template when I took the trace but I don't have any options in Replay Configuration to select events or columns.

Strangely, I also ran a separate trace first and then replay the trace nothing gets traced at all! The events are different though, instead of RPC: Completed I get Replay Result Set Event and Replay Result Row Event, would this make a difference?

Does anyone know how to either amend the columns logged when you replay a trace OR how to set up another trace to trace the replay?